CODEWITHSUNDEEP
powerbipowerquery
Schinken
Schinken

Reputation: 3

Merging multiple parameters into the same row in Power Query

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

Answers (1)

horseyride
horseyride

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 ...

enter image description here

enter image description here

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

Related Questions