Forum Discussion
Paolo82
2 years agoRegular 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 tabl...
- 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.
jgeddes
2 years agoSuper User
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
2 years agoRegular Visitor
Thank you very much, I will try soon but the direction is this one.