In this blog post, well 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.
When I click off to the side in the white space, I can see a preview of that record below.
Lets say I want to extract a single value from this record, and I dont want to expand the entire record. I just want to retrieve the name.
To retrieve a single value from the record, I will use a function called Record.Field. First, well add a custom column.
Second, enter the M function then point to Column1 which contains our records and identifies the field that we want to access.
All we have to do is pass its name as a text value. That field was called Name so well add a closing parentheses and press OK.
As you can see, weve now extracted all those field values.
Instead of a function, I can also use a field access operator to select a value from a record. Again, lets add a custom column.
First, well reference the column containing the record. To access the field, all we need to do is enter the field name inside the square brackets. Well 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 doesnt exist? Well just change this into Name1 and press OK.
Were now getting an error.
To avoid that, all we have to do is add a ? at the end and then press OK.
Basically, were now asking if our field name exists and if it doesnt, we dont get the error, but get a null value instead.
Extracting A Record Field Value
How can we extract a record field value if our records are nested inside the list, and again, we dont 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. Lets 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 dont have a field in my table to identify the zero-based position of the item that I want to access. However, its quite easy to create that helper column. All we need is an index that starts from zero.
Add an index column from zero.
Lets 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. Dont 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 lets 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.
Conclusion
For this tutorial, weve seen two methods on how you can extract values with power query. I hope youve enjoyed this one.
Dont 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 the Enterprise DNAs website for plenty of resources and further learning that you can access very easily.
Melissa
***** 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
***** Related Course Modules *****
Dashboarding & Data Visualization Intensive
Power BI Super Users Workshop
Mastering DAX Calculations
***** Related Support Forum Posts *****
Power Query Extract Values From Lists And records
Create New Records With Excel Power Query
Work Offline In Power Query Editor
For more on power query please see here