Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
Proud to be a 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.
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.
Proud to be a Super User! | |
Thank you very much, I will try soon but the direction is this one.