Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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 IDFirst NameLast Name
15515StellaPeters
15516JanetRoders
15517LukeRoberts

 

This is the child id table

 

Child IDParent IDFirst NameLast Name
1441515515StellaPete
1445615515StellaPeters
1449815516JanetRode
1449915516JanetRoder
1450115517LukeRoberts

 

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

Name IDFirst NameLast Name
15515Stella Peters
14415StellaPete
14456StellaPeters
15516JanetRoders
14498JanetRode
14499JanetRoder
15517LukeRoberts

 

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

 

Cheers

 

1 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.

View solution in original post

11 REPLIES 11
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

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

Capture4.JPG

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
Not applicable

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
Not applicable

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

 

 

 

amitchandak
Super User
Super User

@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

@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

kentyler
Solution Sage
Solution Sage

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.

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

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

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.

 





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.