Power Query Editor Tutorial: Extract Record Field Values From Lists

Power Query Editor Tutorial: Extract Record Field Values From Lists

No comments

In this Power Query Editor tutorial, I want to showcase how you can extract record field values from a nested list and combine those into a single text string. You can watch the full video of this tutorial at the bottom of this blog.

A member of the Enterprise DNA Forum posted this scenario. We have here a table that contains lists. If we drill down into that first list, by right-clicking to the side in the white space and selecting Add as New Query, we can see the content of that list.

power query editor tutorial

This list contains three records. When I click off to the side in the white space here, we can see a preview of the content of that record down below.

power query editor tutorial

The goal is to look up the field lookup value and then extract that value. Once we’ve extracted all those field values, we’re going to combine those into a single text string. So, I’ll reference my source query. I’ll right-click that and select Reference.

power query editor tutorial

Power Query Editor Tutorial – Transforming Nested Lists & Extracting Values

Via that mini table icon, I’ll add a custom column. We want to transform that nested list, extracting each value from that field in those nested records. To do this, we can use a function called list.transform.

power query editor tutorial

This function requires a list, and our lists are in the FromLookup column. So I’ll select that and then put a comma. Next, it wants a transformation as a function. And for each element in that list, we want to get the text from.

So, I’ll write here each Text.From. To access all of the elements, we can use the nameless variable, underscore. So I’ll add the underscore here. But all the elements in our list are records, so we need to also add the field access operator. In a set of square brackets, we can add the field name that we want to access, and that’s called lookupvalue. Close the square bracket and close in parenthesis. Add another closing parenthesis for the List.Transform function, and the press OK.

Let’s click to the side in the wide space to see the results. As you can see, we’ve got all the descriptions from those nested records.

However, we get an error in the third table.

I’ll right-click and add it as a new query. I’ll click off to the side in the wide space and we can see that there’s no lookup value. The field doesn’t exist in this record, so let’s fix that as well.

I’ll delete this helper query, step back to our design query. And after the field access operator, we can simply add the question mark and press enter.

power query editor tutorial

With that, we no longer get that error, but we get a Null instead. All that’s left to do is to combine these text values.

Power Query Editor Tutorial – Combining Values Into A Single Text String

To combine these values into a single string, we can use the function Text.Combine. In the formula bar, we’ll add Text.Combine and then open parenthesis, skip to the end and add a comma.

The first parameter was texts as a list and now it wants a separator as a text. So let’s add quote signs, close in parenthesis, and press enter. And there you have it.

Conclusion

In this tutorial, I’ve shown you how to extract record field values from a nested list and combine those values into the single text string. I’ve used List.Transform and Text.From functions to extract field values for each of the records within that nested list. And finally, I’ve combined those results in a single text string using Text.Combine.

I hope you can use and implement this technique in your own work environment. For more similar tutorials, check out the related links below.

All the best!

Melissa

***** Related Links *****
Extract Values From Records And Lists In Power Query
Power BI Power Query: Removing Duplicate Rows
Power BI Unpivot Columns – Power Query Tutorial

***** Related Course Modules *****
Power Query Series
Advanced Data Transformations & Modeling
Financial Reporting w/Power BI

***** Related Support Forum Posts *****
M Code – Two Criteria for Lookup
Power Query – Extract Values from Lists and Records in Adjacent Column
Two Sets of Relations Against Single Reference Table
For more on power query support queries please see here…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.