Forum Discussion
How to change data (pivot? Group?)
Hi Ka4ra ,
Here are the steps you can follow：
1. Create calculated table.
Table2 = SUMMARIZE( 'Table',[Date],[ID],[VALUE], "Co1",MAX('Table'[Col1]), "Co2",MAX('Table'[Col2]), "Co3",MAX('Table'[Col3]), "Co4",MAX('Table'[Col4]), "Co5",MAX('Table'[Col5]), "Co6",MAX('Table'[Col6]), "Co7",MAX('Table'[Col7]))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
In Power Query, all you need to do is to Group By Date, ID and Value and do a SUM on rest of the columns. Here's a sample M Code. Adjust the source and let it run.
let
Source = Excel.Workbook(File.Contents("C:\tmp\Power BI Samples\Grouping Data\Group Data Sample.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Date", type text}, {"ID", type text}, {"VALUE", Int64.Type}, {"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", type number}, {"Col4", Int64.Type}, {"Col5", Int64.Type}, {"Col6", type any}, {"Col7", type any}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "ID", "VALUE"}, {{"Col1", each List.Sum([Col1]), type nullable number}, {"Col2", each List.Sum([Col2]), type nullable number}, {"Col3", each List.Sum([Col3]), type nullable number}, {"Col4", each List.Sum([Col4]), type nullable number}, {"Col5", each List.Sum([Col5]), type nullable number}, {"Col6", each List.Sum([Col6]), type any}, {"Col7", each List.Sum([Col7]), type any}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"Col1", type number}, {"Col2", type number}, {"Col3", type number}, {"Col4", type number}, {"Col5", type number}, {"Col6", type number}, {"Col7", type number}})
in
#"Changed Type1"