Not @dinetrsp
Create a new query from the "parent id" table,
"Add Index Column" ->"Rename Columns->Add Another Index Column
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1NTRV0lEKLknNyUkEMgJSS1KLipVidSByZkAhr8S81BIgHZSfgixlDhTyKc1OBcskpRaVAKViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Parent ID" = _t, #"First Name" = _t, #"Last Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent ID", Int64.Type}, {"First Name", type text}, {"Last Name", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
n=Table.SelectColumns(#"Added Index",{"Parent ID","First Name","Last Name","Index"}),
#"Renamed Columns" = Table.RenameColumns(n,{{"Parent ID", "Name ID"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Index_2", each 1)
in
#"Added Custom"
Next, add the steps in the "parent id" table,
Add index column > queries from the child table >add a custom column > query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ1NTRV0lEKLknNyUkEMgJSS1KLipVidSByZkAhr8S81BIgHZSfgixlDhTyKc1OBcskpRaVAKViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Parent ID" = _t, #"First Name" = _t, #"Last Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent ID", Int64.Type}, {"First Name", type text}, {"Last Name", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Parent ID"}, #"child table", {"Parent ID"}, "child table", JoinKind.RightOuter),
#"Expanded child table" = Table.ExpandTableColumn(#"Merged Queries", "child table", {"Child ID", "First Name", "Last Name"}, {"child table. Child ID", "child table. First Name", "child table. Last Name"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded child table",{"Parent ID", "First Name", "Last Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"child table. Child ID", "Name ID"}, {"child table. First Name", "First Name"}, {"child table. Last Name", "Last Name"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Name ID", "First Name", "Last Name", "Index"}),
#"Added Custom" = Table.AddColumn(#"Reordered Columns", "Index_2", each 2),
#"Appended Query" = Table.Combine({#"Added Custom", Query1}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"Index", Order.Ascending}, {"Index_2", Order.Ascending}})
in
#"Sorted Rows"
You can download my file and see the details of each step.
Best regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accepting it as the solution to help other members find it more quickly.
