Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

a complex table transformation

I found a not very laborious way to transform the starttab table into the endtab table (which are in the attached excel).

I submit the situation because I think it is quite typical and maybe more canonical and / or more efficient solutions are available.

 

 

Spoiler
in the file there is a hidden sheet with a trace of my solution

 

 

 

 

Tips and Tricks

5 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    Need to have one clarity. In the attached table, values are not matching when we pivot them. 

    What mathematical operation should be performed to get those values.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      You're right.
      I apologize.
      mmmhh ... I made some mess in an attempt to change the numbers (in reality they are real numbers used in the my company to do some analysis).
      Consider only the structure of the start and end tables.
      As soon as I have time I try to fix the data.

      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        https://1drv.ms/x/s!Akd5y6ruJhvhuRjrJ9fC6bjpJQ8i?e=b567lc 

        let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"com"}, {{"Temp", each _, type table [SERV=text, com=text, b=number, w=number]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Added Index", "Temp", {"SERV", "b", "w"}, {"SERV", "b", "w"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Temp", {"com", "SERV", "Index"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[SERV]), "SERV", "Value", List.Sum),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"