Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi! I am want to expend my table which records child-parent relationships.
As you see, I want to create a row with for "abc" and set its parent as null.
How can I do it with powerquery? thanks!
Solved! Go to Solution.
Hi @Anonymous
Modify this line a little bit
Custom1 = List.Distinct(List.Difference(Source[Parent],Source[Child])),
Hi @Anonymous
Find the difference between Child and Parent then Table.Combine
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSklNU9JRSkxKVorViVZKz8gE8kBisbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, Parent = _t]),
Custom1 = List.Difference(Source[Parent],Source[Child]),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Child"}}),
Custom2 = Table.Combine({Source, #"Renamed Columns"})
in
Custom2
Or combine Child and Parent, then Merge
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSklNU9JRSkxKVorViVZKz8gE8kBisbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Child = _t, Parent = _t]),
Custom1 = List.Union({ Source[Child],Source[Parent]}),
#"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Child"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Child"}, Source, {"Child"}, "Renamed Columns", JoinKind.LeftOuter),
#"Expanded Renamed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Renamed Columns", {"Parent"}, {"Parent"})
in
#"Expanded Renamed Columns"
There is a problem with method one.
If "abc" appears twice, two rows will be added.
Hi @Anonymous
Modify this line a little bit
Custom1 = List.Distinct(List.Difference(Source[Parent],Source[Child])),
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |