Easily Extract Data from Power Query Lists and Records

Поделиться
HTML-код
  • Опубликовано: 28 окт 2024

Комментарии • 74

  • @cherylt2322
    @cherylt2322 2 года назад

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

  • @jimfitch
    @jimfitch 2 года назад +3

    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.

  • @RolandMaj7
    @RolandMaj7 Год назад +4

    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.

    • @2signus
      @2signus 3 месяца назад

      I have the same situation

  • @jamesdeanstephens
    @jamesdeanstephens 2 года назад

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

  • @dekmar7954
    @dekmar7954 2 года назад +1

    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.

  • @chakrabmonoj
    @chakrabmonoj 2 года назад

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

  • @nko1084
    @nko1084 Год назад

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

  • @峰王
    @峰王 Год назад

    Great tutorial. Tks for sharing sir!

  • @darrylmorgan
    @darrylmorgan 2 года назад

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

  • @jawadahmadehssan6251
    @jawadahmadehssan6251 2 года назад

    Thanks Mynda, great content as always.

  • @cedjulemckeever
    @cedjulemckeever 2 года назад

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

  • @frankodelossantos2508
    @frankodelossantos2508 2 года назад

    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!!!

  • @JJ_TheGreat
    @JJ_TheGreat 11 месяцев назад

    Very useful, thank you! 😊

  • @janithb1309
    @janithb1309 3 месяца назад

    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.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  3 месяца назад

      Hmm, good question. Not that I'm aware of, at least not natively.

  • @seamushand8439
    @seamushand8439 2 года назад

    Very helpful, thank you

  • @VladLOnFIRE
    @VladLOnFIRE 11 месяцев назад

    thank you. great video.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  11 месяцев назад +1

      Glad you liked it!

    • @VladLOnFIRE
      @VladLOnFIRE 11 месяцев назад

      ​@@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.

  • @danielnoce75
    @danielnoce75 2 года назад

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

    • @philiptreacy714
      @philiptreacy714 2 года назад

      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]

  • @chrism9037
    @chrism9037 2 года назад

    Thanks Mynda!

  • @drewbbc7074
    @drewbbc7074 2 года назад +1

    very nice. Thank you.

  • @JenMayB
    @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.

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Год назад

      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

  • @xplav
    @xplav 2 года назад

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

  • @ankursharma6157
    @ankursharma6157 2 года назад

    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!

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад +1

      Hi Ankur, if you open the Data tab > Queries and Connections, you'll see there are 3 queries containing the data.

    • @ankursharma6157
      @ankursharma6157 2 года назад

      @@MyOnlineTrainingHub Thank You for Your Prompt Response.
      I got it and Practiced and Learnt!
      Gratitude!

  • @iankr
    @iankr 2 года назад

    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?

    • @philiptreacy714
      @philiptreacy714 2 года назад

      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")

  • @stealthsearch2351
    @stealthsearch2351 Год назад

    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?

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Год назад

      Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

    • @stealthsearch2351
      @stealthsearch2351 Год назад

      @@MyOnlineTrainingHub Hi, my question is more of a connecting a SharePoint list to power bi issue. Does that also apply?

  • @matheusefurtado
    @matheusefurtado 8 месяцев назад

    Good tipes! I think that you like The Walkind Dead Series haha

  • @SamehRSameh
    @SamehRSameh 2 года назад

    Great we need more

  • @shahariar9919
    @shahariar9919 2 года назад

    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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад

      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.

    • @shahariar9919
      @shahariar9919 2 года назад +1

      @@MyOnlineTrainingHub Thanks Mynda. Actually I forget to check queries & connection. All of them was there as connection. Anyway it is a great tutorial indeed.

  • @apamwamba
    @apamwamba 2 года назад

    Very NEAT!

  • @GeorgeAJululian
    @GeorgeAJululian 2 года назад

    please help How To Extract Names From Main List Are Not Included In Other 5 Lists

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад

      Hi George, please see this tutorial: www.myonlinetraininghub.com/easily-compare-multiple-tables-in-power-query

  • @shakthiatukorala595
    @shakthiatukorala595 2 года назад

    Great....!👍👍👍

  • @ayazali9890
    @ayazali9890 2 года назад

    so good as you

  • @gianfrancotan4384
    @gianfrancotan4384 Год назад

    How to put list values id, name, email in 1 column? list is not in constant value

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  Год назад

      Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

  • @graphguy
    @graphguy 2 года назад

    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

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад +1

      You can use Split By > Delimiter in Power Query to split by the tilde or :O etc.

  • @abdullah_alhathloul_
    @abdullah_alhathloul_ 2 года назад

    Great

  • @JJ_TheGreat
    @JJ_TheGreat 11 месяцев назад +1

    2:55 try…otherwise 😊

  • @youd.1563
    @youd.1563 2 года назад +3

    Would you please speak more clearly. Sometimes it's really hard to understand you

    • @MyOnlineTrainingHub
      @MyOnlineTrainingHub  2 года назад +1

      Have you tried playing the video at a slower playback speed?

  • @markk364
    @markk364 2 года назад

    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.