Forum Discussion
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...
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.
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.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"