Extract Values From Records And Lists In Power Query

In this blog post, we’ll take a look at how you can extract values from records and lists using Power Query. As you can see, the first column in my table contains records. You can watch the full video of this tutorial at the bottom of this blog.

extract values, power query get single value from table screenshot 1

When I click off to the side in the white space, I can see a preview of that record below.

extract values

Let’s say I want to extract a single value from this record, and I don’t want to expand the entire record. I just want to retrieve the name.

extract values

To retrieve a single value from the record, I will use a function called Record. Field. First, we’ll add a custom column.

extract values, power query extract values from list screenshot 2

Second, enter the M function then point to Column1 which contains our records and identifies the field that we want to access.

extract values

All we have to do is pass its name as a text value. That field was called Name so we’ll add closing parentheses and press OK.

extract values, power bi extract values from list screenshot 3

As you can see, we’ve now extracted all those field values.

extract values

Instead of a function, I can also use a field access operator to select a value from a record. Again, let’s add a custom column.

extract values, extract values from list power query screenshot 4

First, we’ll reference the column containing the record. To access the field, all we need to do is enter the field name inside the square brackets. We’ll write the opening square bracket, the name of the field value that we want to retrieve, the closing square bracket, then press OK.

As you can see, this gets me exactly the same result.

Now, what happens if my field name doesn’t exist? We’ll just change this into Name1 and press OK.

We’re now getting an error.

To avoid that, all we have to do is add a “?” at the end and then press OK.

Basically, we’re now asking if our field name exists and if it doesn’t, we don’t get the error, but get a null value instead.

Extracting A Record Field Value

How can we extract a single record field value if our records are nested inside the list, and again, we don’t want to extract the list and those records first? Well, in that case, we can also use the item access operator to select an item based on its zero-based position within the list. Let’s explore both methods that we used before.

Now, the goal for the first row in the table is to access the first item from the list. The goal for the second row is the second item, and so on.

At this time, I don’t have a field in my table to identify the zero-based position of the item that I want to access. However, it’s quite easy to create that helper column. All we need is an index that starts from zero.

Add an index column from zero.

Let’s add a custom column.

Enter the M function to the column that contains our lists which is Column2. Use curly brackets and pass our index column. Don’t forget the closing curly bracket, comma, and then again, pass the field name as a text value. Type Name, use closing bracket, and press OK.

Instead of the function, we can also use that combination of item and field access operators to achieve the same result. So let’s add a custom column, and point to the column that contains the lists, which is Column2.

Inside those curly brackets, we can access the item. We want to access the field called Name.

***** Related Links *****
Creating Values For Each Date Using Power Query Formula
Delimited Data – Extracting Records Using Power BI
Extracting Unique Insights In Power BI Using Ranking DAX Measures

Conclusion

For this tutorial, we’ve seen two methods on how you can extract values with power query. I hope you’ve enjoyed this one.

Don’t forget to subscribe to the Enterprise DNA TV channel. We have a lot of content coming out from myself and from the Enterprise DNA experts, all dedicated to improving the way that you use Power BI.

Lastly, check out Enterprise DNA’s website for plenty of resources and further learning that you can access very easily.

Melissa

Enterprise DNA Power BI On-Demand

The Latest

As you continue your journey as a Python programmer, you’ll want to write code that is more efficient, readable, and…

Python Inline If: Simplify Your Conditional Expressions

You’ve been cruising through your Python journey, slicing through lists, taming those wild tuples, and maybe even wrestling with a…

Python Empty String: Understanding and Handling It Effectively

Power BI financial dashboards provide a quick and easy way to monitor an organization’s financial performance in real-time. By consolidating…

Power BI Financial Dashboard Examples: Key Insights for Businesses

When working with integers in Python, you should know the maximum value your code can handle. This will depend on…

Python Max Int: Understanding Maximum Integer Limits

Pi is a fundamental mathematical constant that represents the ratio of a circle’s circumference to its diameter. Leveraging Pi in…

4 Ways to Use Pi in Python With Examples

ChatGPT is an advanced AI-powered tool that can transform the way you write code. Developed by OpenAI, ChatGPT accelerates your…

ChatGPT for Coding: A Guide With Practical Examples

When working with data projects in Python, you are most likely to use datetime functions. Almost every dataset that you’ll…

Python datetime, a comprehensive guide with examples

Power BI is a powerful business analytics tool that helps you visualize and analyze data from various sources. One of…

Power BI Themes: How to Customize Your Reports with Ease

With the advent of ChatGPT, individuals and businesses worldwide have been using it to simplify their daily tasks and boost…

ChatGPT for Data Scientists: Unleashing AI-driven Insights

Staying ahead of the curve in data analysis is essential to your success in business. One of the most innovative…

ChatGPT for Data Analysts: Revolutionizing Insights and Reporting

Imagine being able to look at your data from every which way — from the bird’s eye view right down…

Power BI Hierarchy: Unlocking Levels and Drill-Downs in Visualizations

As you explore Python and take on more complex projects, you’ll notice that some projects have lots of files. Managing…

os.path.join: Simplifying File Path Operations in Python

Load More