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 and right-click to the side in the white space and select Add as New Query, we can see the content of that list.
This list contains three records. When I click off to the side in the white space, we can see a preview of the content of that record down below.
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 – 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.
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 it.
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 then 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.
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.
***** 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
Conclusion
In this tutorial, I’ve shown you how to extract record field values from a nested list and combine those values into a 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
