Extract List of Records

[gadlakha]

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.

[gadlakha]

Yes, but that created multiple rows for each KEY

[m_dekorte]

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!

[ppm1]

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

Pat