Easily Extract Data from Power Query Lists and Records

Lists and Records are a key component of working with Power Query. You may have seen them when getting data from SharePoint or an API. In this video we'll show you how to easily extract the data with a Custom Column.

0:00 Introduction
0:22 Get data from Lists
3:16 Get data from Records
5:56 Get data from Lists & Records

[jimfitch]

Great tutorial. And timely, too. I especially liked the third example that has a single step that produces combined result whether source data are Lists or Records. A simple nested if-then-else, but clever.

[cherylt]

Thanks Mynda! Also, wanted to mention that I appreciate the new "Bite Size Excel Tips"! Keep them coming please.

[jawadahmadehssan]

Thanks Mynda, great content as always.

[RolandMaj]

Hello, I'm new to this. I want to understand a deeper navigation into nested lists of records. For example, how to do this exact thing if I have a List, that consists of records, which are lists of more records that I actually need as column titles field value). I'm receiving quite complicated and nested JSON from an API that I want to sort in an understandable way and view in Excel.

[darrylmorgan]

Hi Mynda!Really Excellent And Helpful Tutorial From Phil...Thank You Both :):)

[chakrabmonoj]

Excellent lesson - the most comprehensive one I have seen on this complex subject of accessing items in records, lists & tables.

[cedjulemckeever]

Always good information. I look forward to your Power Query tutorials.

[nko]

many thanks indeed. Can I apply this to extracting data from Table in Table

[dekmar]

Last example, could simplify to = if [Data] is list then [Data]{1} else if [Data] is record then [Data][Name] else "". Great video! The { } brackets either create a list or select from a list/table (numbered thing) and the [ ] brackets either create a record or select from a record/table (named thing). Here [Data] is shorthand for _[Data], where the data field is being selected from the row record.

[frankodelossantos]

Hi would like to ask if this approach is dynamic. Meaning, if there is newly added data in the table, is it going to reprocess it to extract the list/record? Thanks. ☺️ This was very helpful!!!

[xplav]

2:38 what if i want extract some data inside in the list on the 4th line?

[iankr]

Thanks for this. Perhaps I'm being confused by the nested entities and syntax here, but if the person's name is in the second entry in the List (the first is the ID), why isn't it {1} rather than {0} to extract the name?

[danielnoce]

This is very helpful. I am curious though... is there a method to get data from Lists & Tables as well?

[jamesdeanstephens]

Very good. I especially like the fact that Phil used characters from the Walking Dead in his examples. LOL.

[JenMayB]

Thank you so much. What if I have a list that contains a list of values and I want them all returned with the delimiter. So I can’t specify where I want a value returned, I want them all returned.

[janithb]

Is there a way to get a progress bar in excel while the power query action is running in the background? So to let us know of the percentage completed.

[stealthsearch]

HI, I have a similar problem where i'm trying pull in 9 line items form a list column. The values in the list column are from a drop down list so i need to pull all the information from that column. Can you please provide some assistance as to how i could go about it?

[shahariar]

Not getting the practice file in the given link....where I can get the the practice file the link provide me list of new videos suggestion where I can get the

[ankursharma]

Hi Mynda,

I am Your Grateful Subscriber. Have learnt immensely from Your tutorials.

I clicked on the link to download the Practice Workbook . . instead, the Workbook contains links to Resources, Courses, etc. There is no data to Practice. I would be grateful if You fix this 'coz I want to PRACTICE and learn.

Thank You!

[graphguy]

I'm good with the tools and techniques I have learned watching your expertise in action!
But I am struggling with string/text extraction in fields that is highly variable, but yet does have some predictable delimiters.
For example the following cell below:
I need to extract all examples of 'words' left delimiter by either a "~" or a ", " and delimited on the right by ":O". The position is always variable and there is never a definitive logic if the left delimiter is a ", " or a "~".
BT#BT, \R2#R2, R2\BT#ANASP, \R3#>100, SSABIL:O-RO\BT#ANASP, \R4#>100, SSBBIL:O-RO\BT#ANASP, \R5#>100, SMBILL:O-RO\BT#ANASP, \R6#>100, RNPBIL:O-RO\BT#ANASP, \R7#>100, SCLBIL:O-RO\BT#ANASP, \R8#>100, JOBILL:O-RO\BT#ANASP, \R9#>100, CNTBIL:O-RO\BT#ANASP, \R10#>100, HSTBIL:O-R