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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DiKi-I
Helper III
Helper III

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 III
Helper III

@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 III
Helper III

How I can do it in power query? 

DiKi-I
Helper III
Helper III

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors