cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
forever
Frequent Visitor

Power Query with add rows with conditions

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!

forever_2-1626677703664.png

 

 

1 ACCEPTED SOLUTION

Hi @forever 

 

Modify this line a little bit

 Custom1 = List.Distinct(List.Difference(Source[Parent],Source[Child])),

View solution in original post

3 REPLIES 3
Vera_33
Super User
Super User

Hi @forever 

 

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"

 

forever
Frequent Visitor

There is a problem with method one.

If "abc" appears twice, two rows will be added.

forever_0-1626682592073.png

 

Hi @forever 

 

Modify this line a little bit

 Custom1 = List.Distinct(List.Difference(Source[Parent],Source[Child])),

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors