Forum Discussion

gadlakha's avatar
gadlakha
Frequent Visitor
3 years ago

Extract List of Records

Hey,

I have a data where in each row could have a list of records associated with it.
How can I extract each record from the list and add to the corrosponding row to be further expanded into each data value?

I need each row to have all corrosponding Module, GitReview, GitTag and PoC information for all the associated records from the list.

 

Need Help

3 Replies

  • ppm1's avatar
    ppm1
    Solution Sage
    3 years ago

    Have you tried to expand that list (double arrow in column header) to new rows and then expand the Records column?

     

    Pat

    • m_dekorte's avatar
      m_dekorte
      Resident Rockstar
      3 years ago

      Hi gadlakha 

       

      Give this a go. You can copy the full sample script below into a new blank query.

       

      let
    Source = Table.FromColumns(
        {
            {1},
            {{[Module = "com", GitTag=2, Other = "text1"], [Module = "com", GitTag=3, Other = "text2"], [Module = "com", GitTag=4, Other = "text3"], [Module = "com", GitTag=5, Other = "text4"]}}
        },
        type table [ Key = number, Changes = list]
    ),
    ExtractRecords = List.Accumulate( Record.FieldNames( Source[Changes]{0}{0} ), Source,
        (s, a)=> Table.AddColumn( s, a, (x)=> Text.Combine( List.Transform( x[Changes], (y)=> Text.From( Record.Field( y, a ))), ", "), type text )
    )
in
    ExtractRecords

       

       

      It returns the fieldnames from the first nested record in "Changes" list (you could replace this with a hard coded list of record field names you want to extract)

      and adds a new column to show all values in a single cell.

       

       Ps. If this helps solve your query please mark this post as Solution, thanks!