Power query help

[DiKi-I]

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

[wdx223_Daniel]

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]

see latest post

[DiKi-I]

 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 

[spinfuzer]

see latest reply

[spinfuzer]

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?

[DiKi-I]

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.

[spinfuzer]

see latest reply

[DiKi-I]

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

 

[spinfuzer]

merging by parent and parentclassname instead of just parent.

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"

[spinfuzer]

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]

Are there ever multiple children to a parent?

[DiKi-I]

I have updated the sample data.

[DiKi-I]

yes one parent can have multiple children

[DiKi-I]

@adudani can you help on this pls?

[adudani]

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:

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

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"

[DiKi-I]

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.

let me if this explains the scenario.

[DiKi-I]

How I can do it in power query? 

[DiKi-I]

I have created output tab in the excel.

[PijushRoy]

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.