Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Paolo82
Regular Visitor

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

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

If you have tableX that looks something like...

jgeddes_0-1708104763212.png

You can get a tableY that looks like...

jgeddes_1-1708104863300.png

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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

jgeddes
Super User
Super User

If you have tableX that looks something like...

jgeddes_0-1708104763212.png

You can get a tableY that looks like...

jgeddes_1-1708104863300.png

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.




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors