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 IDFirst NameLast Name15515StellaPeters15516JanetRoders15517LukeRoberts This is the child id table Child IDParent IDFirst NameLast Name1441515515StellaPete1445615515StellaPeters1449815516JanetRode1449915516JanetRoder1450115517LukeRoberts I want to create a table that stacks child ids under parents ids as below.Name IDFirst NameLast Name15515Stella Peters14415StellaPete14456StellaPeters15516JanetRoders14498JanetRode14499JanetRoder15517LukeRoberts I hope someone can guide me with this? Many thanks in advance. Cheers

Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Stack Child Ids under Parent Ids in a PowerBi table

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

Need Help

v-juanli-msft

6 years ago

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.

Stack Child Ids under Parent Ids in a PowerBi table.pbix57 KB

11 Replies

amitchandak
Super User
6 years ago
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
  Not applicable
  6 years ago
  amitchandak Thank you very much. Do I need to use your code as a new measure or a new table in PowerBi?
  - amitchandak
    Super User
    6 years ago
    Anonymous , New table

v-juanli-msft

Community Support

6 years ago

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.

Stack Child Ids under Parent Ids in a PowerBi table.pbix57 KB

Anonymous

Not applicable

6 years ago

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

Community Support

6 years ago

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.

Stack Child Ids under Parent Ids in a PowerBi table.pbix57 KB

kentyler
Solution Sage
6 years ago
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
  Not applicable
  6 years ago
  Thanks a lot kentyler . I need your assistance with both of these options.
  - kentyler
    Solution Sage
    6 years ago
    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 : [email protected] and a good time to talk and I'll send you a ZOOM invitation.

Forum Discussion

Stack Child Ids under Parent Ids in a PowerBi table

11 Replies

Related content

Stacking Data Columns

Parent/child hierarchy creation

POWERBI Subscription

Parent pipeline invokes old version of child pipeline until parent is re-saved

Stacked Column Chart Data Labels