Forum Discussion
How to change data (pivot? Group?)
I want to change the data format.
Below is sample data. Data has a same ID, Value but Col1 to Col7 some have data and some do not.
I'm doing data transformation in Power BI, but I don't know what to do.
| Date | ID | VALUE | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 |
| 23-01-01 2:40 PM | AAAAA3 | 20 | null | 2 | 25 | null | null | null | null |
| 23-01-01 2:40 PM | AAAAA3 | 20 | 15 | null | null | null | 20 | null | null |
| 23-01-01 2:40 PM | AAAAA3 | 20 | null | null | null | 52 | null | null | null |
| 23-01-02 12:10 PM | AVSV2 | 49 | 10 | 21 | null | 26 | null | null | null |
| 23-01-02 12:10 PM | AVSV2 | 49 | null | null | null | null | null | null | null |
| 23-01-02 12:10 PM | AVSV2 | 49 | null | null | 0.1 | null | null | null | null |
I want to change the format data look like below.
I would like to put the data of each column matching the ID into one ID row for each column.
| Date | ID | VALUE | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 | Col7 |
| 23-01-01 2:40 PM | AAAAA3 | 20 | 15 | 2 | 25 | 52 | 20 | null | null |
| 23-01-02 12:10 PM | AVSV2 | 49 | 10 | 21 | 0.1 | 26 | null | null | null |
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
2 Replies
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"
