Forum Discussion

19 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    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.

    • adudani's avatar
      adudani
      Memorable Member
      2 years ago

      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's avatar
        DiKi-I
        Post Partisan
        2 years ago

        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's avatar
      DiKi-I
      Post Partisan
      2 years ago

      yes one parent can have multiple children

    • DiKi-I's avatar
      DiKi-I
      Post Partisan
      2 years ago

       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's avatar
        spinfuzer
        Solution Sage
        2 years ago

        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?

         

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    2 years ago

    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