Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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.
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.
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!
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 ,
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.
@amitchandak Thank you very much. Do I need to use your code as a new measure or a new table in PowerBi?
@Anonymous , New table
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.
Help when you know. Ask when you don't!
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.
Help when you know. Ask when you don't!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
89 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
141 | |
110 | |
69 | |
55 |