Forum Discussion

Paolo82's avatar
Paolo82
Regular Visitor
2 years ago
Solved

New table containnig objects in a [List]

Dear community, I'm brand new in PBI and PowerQuery and this is my topic: I have a dataset from a connector of a third part vendor I need to use to collect data. This dataset is just a simple tabl...
  • jgeddes's avatar
    2 years ago

    If you have tableX that looks something like...

    You can get a tableY that looks like...

    with the following code...

    let
        Source = tableX,
        #"Expanded Object" = 
        Table.ExpandListColumn(
            Source, 
            "Object"
        ),
        #"Added Index" = 
        Table.AddIndexColumn(
            #"Expanded Object", 
            "tableY ID", 
            1, 
            1, 
            Int64.Type
        ),
        #"Added Prefix" = 
        Table.TransformColumns(
            #"Added Index", 
            {{"tableY ID", each "ID_" & Text.PadStart(Text.From(_), 4 - Text.Length(Text.From(_)), "0"), type text}}
        ),
        #"Reordered Columns" = 
        Table.ReorderColumns(
            #"Added Prefix",
            {"tableY ID", "tableX ID", "Object"}
        )
    in
        #"Reordered Columns"

    If you did not want tableX IDs with no objects in their list to get a tableY ID you would need to add a step after 'Expanded Object' that filters out those rows (null rows).
    Hope this gets you pointed in the right direction.