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 c...
Tips and Tricks
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
Icon for Most Valuable Professional rankMost 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"