Power query help

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"