Reputation: 3
I got the following table I want to edit with power query (still a beginner). Every month we got a new row for every parameter (many) for every object. What I want, is a table with a new row for every every object and every Month with all the parameters listed as columns. The parameters can include numbers, dates, empty values etc.
I hope I could explain my issue well enough. Thanks for you help!
What I have:
Parameter | Object | Location | Size | Month | Value |
---|---|---|---|---|---|
1 | Object A | USA | 4 | Jan 2002 | 180 |
1 | Object A | USA | 4 | Feb 2002 | 210 |
2 | Object A | USA | 4 | Jan 2002 | 312 |
2 | Object A | USA | 4 | Feb 2002 | 140 |
1 | Object B | CAN | 6 | Jan 2002 | 164 |
1 | Object B | CAN | 6 | Feb 2002 | 130 |
2 | Object B | CAN | 6 | Jan 2002 | 95 |
2 | Object B | CAN | 6 | Feb 2002 | 122 |
What I want:
Object | Month | Location | Size | Parameter 1 | Parameter 2 | Parameter 3... |
---|---|---|---|---|---|---|
Object A | Jan 2002 | USA | 4 | 180 | 312 | ... |
Object A | Feb 2002 | USA | 4 | 210 | 140 | ... |
Object B | Jan 2002 | CAN | 6 | 164 | 95 | ... |
Object B | Feb 2002 | CAN | 6 | 130 | 122 | 95 |
Upvotes: 0
Views: 1097
Reputation: 21428
Load data into powerquery with data .. from table/range... [x] headers
click select parameter column
transform .. pivot column
values column:value [ok]
file ... close and load ...
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parameter", Int64.Type}, {"Object", type text}, {"Location", type text}, {"Size", Int64.Type}, {"Month", type datetime}, {"Value", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Parameter", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Parameter", type text}}, "en-US")[Parameter]), "Parameter", "Value", List.Sum)
in #"Pivoted Column"
Upvotes: 1