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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
DiKi-I
Helper IV
Helper IV

Power query help

Hi ,

I have multiple relationship table around 5 to 6 which are available in mutiple tables.
I'm attaching some sample data there are 3 tables table1, table2, table3.
Realtionship is from parent class to child class 

business service->business service line -> department -> IT.
Can someone please help me in the data prepartion ?
https://docs.google.com/spreadsheets/d/1kQODqFakeG88QsMegk5URHtKLiZLyUq-/edit?usp=drive_link&ouid=10...

19 REPLIES 19
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1702442801530.png

let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Table3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Custom1 = Table.Combine(List.Transform(List.Accumulate({Table1,Table2,Table3},null,(x,y)=>let a=Table.ToRows(y) in if x=null then a else List.TransformMany(x,each let b=List.Select(a,(x)=>List.FirstN(x,2)=List.LastN(_,2)) in if b={} then {{null,null}} else b,(x,y)=>x&List.LastN(y,2))),each let a=List.RemoveNulls(_) in #table(List.Alternate(a,1,1),{List.Alternate(a,1,1,1)})))
in
    Custom1

 

spinfuzer
Super User
Super User

see latest post

 I tried with only two tables initially but its not loading and taking forever.
There is no circular dependency (A->B)
There are around 2k records 

see latest reply

Are you sure you are referring to the right columns in the join and not inadvertantly going from A --> A --> A--> A in an infinite loop instead of A --> B?

 

I added 500 random records and it ran just fine.  Do you have a larger data sample?

 

Hi 
I checked the data the classname is different but in some cases the parent and child will be same.
For those entries I am getting many rows.
Hierarachy can be same for some records.



see latest reply

Hi,

I tested the above and now I am getting the result quickly but if the values are same I am getting null in the child only getting the value in the first parent.
values are below in my data
business service= A

business service line =A

department = A
and I final result I am getting from you m query is: 

business service= A

business service line =null

department = null

 

 

my data is like this

 

merging by parent and parentclassname instead of just parent.

 

spinfuzer_0-1702432094568.png

 

 

 

 

 

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUUoqLc7MSy0uVihOLSrLTE4FCrlgEVbIAXKVYnWilZywa3LFq8kZuyY3cjS5k6MpAq8mRxwhgVUcqi0WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Parentclassname = _t, Child = _t, Childclassname = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclHSUUoqLc7MSy0uVihOLSrLTE5VyAFygeIeQJySWpBYVJKbmleiFKsTreSKR7knpnI3PMq9MJW741HuTZpyH0zlEXiUR4HcHwJW5uiIRx1YEtncWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Parentclassname = _t, Child = _t, Childclassname = _t]),
    Table3 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8lDSUUpJLUgsKslNzSsBcnyB2DNEKVYnWskTXc4PIeeFLuePkPNGlwvAIxeIkPNBlwuCysUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Parent = _t, Parentclassname = _t, Child = _t, Childclassname = _t]),
    Combined = Table.Buffer(Table.Combine({Table1,Table2,Table3})),
    tree = (tbl as table, optional n as number) =>
        let
            update_tree =
                
                if List.Contains(Table.ColumnNames(tbl),"tree")
                then 
                    // Change to Parent Columns
                    Table.ReplaceValue(tbl, each [tree], each Record.AddField([tree], [Parentclassname], [Parent]), Replacer.ReplaceValue,{"tree"})
                else    
                    Table.AddColumn(tbl,"tree", each Record.AddField([], [Parentclassname], [Parent])), // Change to Parent Column
            removeOldParent = Table.RemoveColumns(update_tree, {"Parent", "Parentclassname"}), // Change to Parent Column
            renameChildToParent = Table.RenameColumns(removeOldParent, {{"Child","Parent"},{"Childclassname","Parentclassname"}}),
            getNextChild = if List.NonNullCount(renameChildToParent[Parent]) <> 0 and n < 10 // remove and n < 10 after checking for loops
                then 
                    @tree(
                        Table.ExpandTableColumn(
                            Table.NestedJoin(
                                renameChildToParent,{"Parent","Parentclassname"}, // Change to Parent Column
                                Combined,{"Parent","Parentclassname"}, // Change to Parent Column
                                "nextChild",
                                JoinKind.LeftOuter
                            )
                            ,
                            "nextChild",
                            {"Child","Childclassname"} // Change to Child Column

                        )
                    , n+1 // remove this check
                    )
                else Table.SelectColumns(renameChildToParent,"tree")
        in
            getNextChild,
    final = tree(Table1,0), // remove the ,0 after fixing query and fixing infinite loops
    #"Expanded tree" = Table.ExpandRecordColumn(final, "tree", {"business service", "business service line", "department", "IT"}, {"business service", "business service line", "department", "IT"})        

in
    #"Expanded tree"

 

 

 

Are you saying you have something like

Parent = AA, Parent Class Name = Business Service

Child = BB, Parent Class Name = Business Service

 

What is your expected result there?

spinfuzer
Super User
Super User

Are there ever multiple children to a parent?

I have updated the sample data.

yes one parent can have multiple children

DiKi-I
Helper IV
Helper IV

@adudani can you help on this pls?

hi @DiKi-I ,

 

I would:

1. Merge "parent" in Table 3 with "child" table 2. Expand table 3 ( Child and Child Class name)

2. Merge "parent" in Table 2 with "child" in table 1. Expand table 2 ( Child, Childclassname, Table3.Child, Table3.ChildClassname) Which is shown below:

adudani_1-1702162267173.png

 

Now you could remove the parent&childclass name columns and rename other columns to get :

 

adudani_2-1702162394541.png

 

Note: This is currently not dynamic and is not ideal if there is more than one department.

However, If this meets your requirements, I will explore making it more dynamic.

Let me know.

 

I am pasting the steps from the advanced editor for the tables below:

 

table1

let
    Source = Excel.Workbook(File.Contents("C:\Users\Avi\Downloads\Data1.xlsx"), null, true),
    Table1_Sheet = Source{[Item="Table1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Parent", type text}, {"Parentclassname", type text}, {"Child ", type text}, {"Childclassname", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Parent] <> null)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Child "}, #"Table 2", {"Parent"}, "Table 2", JoinKind.LeftOuter),
    #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Child ", "Childclassname", "Table 3.Child ", "Table 3.Childclassname"}, {"Table 2.Child ", "Table 2.Childclassname", "Table 2.Table 3.Child ", "Table 2.Table 3.Childclassname"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Table 2",{"Parentclassname", "Childclassname", "Table 2.Childclassname", "Table 2.Table 3.Childclassname"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Table 2.Table 3.Child ", "IT"}, {"Table 2.Child ", "Department"}, {"Parent", "Business service"}, {"Child ", "Business Service Line"}})
in
    #"Renamed Columns"

table2:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Avi\Downloads\Data1.xlsx"), null, true),
    Table1_Sheet = Source{[Item="Table2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Parent", type text}, {"Parentclassname", type text}, {"Child ", type text}, {"Childclassname", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Parent] <> null)),
    #"Merged Queries" = Table.NestedJoin(#"Filtered Rows", {"Child "}, #"Table 3", {"Parent"}, "Table 3", JoinKind.LeftOuter),
    #"Expanded Table 3" = Table.ExpandTableColumn(#"Merged Queries", "Table 3", {"Child ", "Childclassname"}, {"Table 3.Child ", "Table 3.Childclassname"})
in
    #"Expanded Table 3"

table3:

let
    Source = Excel.Workbook(File.Contents("C:\Users\Avi\Downloads\Data1.xlsx"), null, true),
    Table1_Sheet = Source{[Item="Table3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Table1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Parent", type text}, {"Parentclassname", type text}, {"Child ", type text}, {"Childclassname", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Parent] <> null))
in
    #"Filtered Rows"

 

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a Kudos 🙂
Kind Regards,
Avinash

Thanks for solution. I also have to handle one scenario where one table has mutiple child class. In that case I have to check the parent of the previous parent doesn't exist then only the child class will be used. I have updated the sheet. 

normally the hierarchy is business service->business service line -> department -> IT but

in some cases it may be business service->business service line --> IT. That's why multiple child class exist in the table 2 .
eg 
like Z is mapped directly to IT instead of department. So if the department is not mapped then map to child else if department exist then ignore the child 'IT' in this case.

Dims_0-1702167059444.png

 


let me if this explains the scenario.

DiKi-I
Helper IV
Helper IV

How I can do it in power query? 

DiKi-I
Helper IV
Helper IV

I have created output tab in the excel.

PijushRoy
Super User
Super User

Hi @DiKi-I 

Your requirement is not clear to me, can you please share more details about the expected output, and what is you looking for data preparation.




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.