Stack Child Ids under Parent Ids in a PowerBi table

[Anonymous]

Hi experts

I am wanting to combine two tables where one table has got  parent ids and the other table with child ids. This is to show the duplicate values we have under one parent id. Below is a sample of the table with parent ids.

Parent ID	First Name	Last Name
15515	Stella	Peters
15516	Janet	Roders
15517	Luke	Roberts

This is the child id table

Child ID	Parent ID	First Name	Last Name
14415	15515	Stella	Pete
14456	15515	Stella	Peters
14498	15516	Janet	Rode
14499	15516	Janet	Roder
14501	15517	Luke	Roberts

I want to create a table that stacks child ids under parents ids as below.

Name ID	First Name	Last Name
15515	Stella	Peters
14415	Stella	Pete
14456	Stella	Peters
15516	Janet	Roders
14498	Janet	Rode
14499	Janet	Roder
15517	Luke	Roberts

I hope someone can guide me with this? Many thanks in advance.

Cheers

[v-juanli-msft]

Hi @Anonymous 

To get result below, i create queries in power query.

If you are interested with my solution, please let me know, i would explain for you with details.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft  this is great. I would appreciate if you can explain the solution. Thank you very much for sharing the file as well.

Cheers!

[v-juanli-msft · Accepted solution]

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.

[Anonymous]

Many thanks for sharing your solution @v-juanli-msft . Appreciate your time and effort 🙂

[amitchandak]

@Anonymous ,

In dax you can do like

union (
summarize(table1,table1[Parent ID],table1[First Name],table1[Last Name]),
summarize(table2,table2[Child  ID],table2[First Name],table2[Last Name])
)

You can also refer :

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

Appreciate your Kudos.

[Anonymous]

@amitchandak  Thank you very much. Do I need to use your code as a new measure or a new table in PowerBi?

[amitchandak]

@Anonymous , New table

[kentyler]

I think a solution that will simplify your problem would be to create a row for every parent that makes the parent also the child of itself.

15515	0	Stella	Pete
14456	15515	Stella	Peters

14456

15515

Stella

Peters

You can do that as part of the ETL process. I imagine you could also do it in M as a way of transforming the table as you load it. If you need help with either option, let us know.

[Anonymous]

Thanks a lot @kentyler . I need your assistance with both of these options.

[kentyler]

Do you want to do a screen share tomorrow morning ? I am in PST zone and I'm available from 7 in the morning to 5 at night. If so send me an email :  ken@8thfold.com and a good time to talk and I'll send you a ZOOM invitation.

[Anonymous]

Thanks @kentyler for your reply. I will contact you in due course.