Stack Child Ids under Parent Ids in a PowerBi table

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.