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 table X with

- a table ID (X_ID)

- one column named Z which contains an object [List] (with zero o more then one object inside)

 

First step:

I would like to create an additional table Y which has to contains:

- Y_ID unique and incremental

- X_ID (the same ID of the table X) to allow the join between X and Y

- each object in the [List]

 

The results should be that this Y table will be like this
Y_ID001   ---  X_ID01  --- Object 1

Y_ID002   ---  X_ID01  --- Object 2

Y_ID003   ---  X_ID02  --- Object 1
...

Second step:
when the dataset of X will be updated also the table Y has to be updated too, creating automatically a new Y_ID with all the related objects in the [List] per each new X_ID

 

I hope someone will be able to help me.

Thank you so much

 

Table Y

Need Help
  • jgeddes's avatar
    jgeddes
    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.

3 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    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.

    • Paolo82's avatar
      Paolo82
      Regular Visitor
      2 years ago

      Thank you very much, I will try soon but the direction is this one.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    You can expand lists to new columns or new rows.  In your case, use the advanced settings to expand the lists to new rows.