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.
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 (List-Record-List-Record-Actual 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.
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.
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!!!
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.
@@MyOnlineTrainingHubyes I was stuck in excel with power editor, had both list and record in the same column.... This helped me get the data out of it.
Hi Daniel, not quite sure what you mean - do you have an example? If you have a table in a column you can access the data inside the table directly. For example you can get an entire column called 'Project Manager' (which is returned as a list) from a table in a column called 'Custom' like this: =[Custom][Project Manager] Because a column is a list you can return the first item from a column using this =[Custom][Data]{0} In my sample file you can get the name inside the record inside the list inside the table using this = [Custom][Data]{0}{0}[Name]
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.
Not sure what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
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!
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?
Hi Ian, Each list in the Data column is a list of records. Inside each of those records are the ID, name etc. So [Data]{0} is getting the first record from the list. You get the Name by using Record.Field( [Data]{0} , "Name")
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?
Not getting the practice file in the given link....where I can get the file......instead the practice file the link provide me list of new videos link......any suggestion where I can get the file........Thanks
The link in the video description takes you to the written version of this tutorial and under the video on that page there's a link to download the file.
@@MyOnlineTrainingHub Thanks Mynda. Actually I forget to check queries & connection. All of them was there as connection. Anyway it is a great tutorial indeed.
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
Thanks Mynda! Also, wanted to mention that I appreciate the new "Bite Size Excel Tips"! Keep them coming please.
Great to hear, Cheryl!
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.
Awesome to hear, Jim!
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 (List-Record-List-Record-Actual 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.
I have the same situation
Very good. I especially like the fact that Phil used characters from the Walking Dead in his examples. LOL.
😁 glad you liked it, James!
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.
Thanks Dekmar
Excellent lesson - the most comprehensive one I have seen on this complex subject of accessing items in records, lists & tables.
Wow, thanks!
many thanks indeed. Can I apply this to extracting data from Table in Table
Great tutorial. Tks for sharing sir!
Glad you liked it 🙏😊
Hi Mynda!Really Excellent And Helpful Tutorial From Phil...Thank You Both :):)
Glad you enjoyed it, Darryl!
Thanks Mynda, great content as always.
I appreciate that, Jawad!
Always good information. I look forward to your Power Query tutorials.
Great to hear!
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!!!
Yes it will 😊
Very useful, thank you! 😊
Glad it was helpful! 😊
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.
Hmm, good question. Not that I'm aware of, at least not natively.
Very helpful, thank you
Glad to hear it!
thank you. great video.
Glad you liked it!
@@MyOnlineTrainingHubyes I was stuck in excel with power editor, had both list and record in the same column.... This helped me get the data out of it.
This is very helpful. I am curious though... is there a method to get data from Lists & Tables as well?
Hi Daniel,
not quite sure what you mean - do you have an example?
If you have a table in a column you can access the data inside the table directly. For example you can get an entire column called 'Project Manager' (which is returned as a list) from a table in a column called 'Custom' like this: =[Custom][Project Manager]
Because a column is a list you can return the first item from a column using this =[Custom][Data]{0}
In my sample file you can get the name inside the record inside the list inside the table using this = [Custom][Data]{0}{0}[Name]
Thanks Mynda!
Our pleasure, Chris!
very nice. Thank you.
Cheers, Drew!
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.
Not sure what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
2:38 what if i want extract some data inside in the list on the 4th line?
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!
Hi Ankur, if you open the Data tab > Queries and Connections, you'll see there are 3 queries containing the data.
@@MyOnlineTrainingHub Thank You for Your Prompt Response.
I got it and Practiced and Learnt!
Gratitude!
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?
Hi Ian, Each list in the Data column is a list of records. Inside each of those records are the ID, name etc. So [Data]{0} is getting the first record from the list. You get the Name by using Record.Field( [Data]{0} , "Name")
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?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Hi, my question is more of a connecting a SharePoint list to power bi issue. Does that also apply?
Good tipes! I think that you like The Walkind Dead Series haha
😁 glad you enjoyed them 🙏
Great we need more
Glad you liked it, Sameh!
Not getting the practice file in the given link....where I can get the file......instead the practice file the link provide me list of new videos link......any suggestion where I can get the file........Thanks
The link in the video description takes you to the written version of this tutorial and under the video on that page there's a link to download the file.
@@MyOnlineTrainingHub Thanks Mynda. Actually I forget to check queries & connection. All of them was there as connection. Anyway it is a great tutorial indeed.
Very NEAT!
Thank you! Cheers!
please help How To Extract Names From Main List Are Not Included In Other 5 Lists
Hi George, please see this tutorial: www.myonlinetraininghub.com/easily-compare-multiple-tables-in-power-query
Great....!👍👍👍
Thanks 😊
so good as you
Glad you liked it!
How to put list values id, name, email in 1 column? list is not in constant value
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
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
You can use Split By > Delimiter in Power Query to split by the tilde or :O etc.
Great
Cheers, Abdullah!
2:55 try…otherwise 😊
Would you please speak more clearly. Sometimes it's really hard to understand you
Have you tried playing the video at a slower playback speed?
It is entirely unclear what you are trying to accomplish here - try starting with showing a solution, then have your little dude talk about it.