How to change data (pivot? Group?)

[Ka4ra]

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

[v-yangliu-msft · Accepted solution]

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

[amustafa]

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"