Pivot Column Question

[Craigc3814]

I assume this is a very easy thing to do and I am just missing what I want for some reason. I am using PowerQuery and Excel 2016 (Office 365)

I have 5 columns of data that look like below

Location Usage Date BMD IMD PMD
Home January 50 100 100
Home February 75 80 80


I want to transform (I assume Pivot with no aggregation) to this

Location Type January February March April......
Home BMD 50 75
Home IMD 100 80
Home PMD 100 80

 

[Craigc3814]

Figured it out needed to unopivot BMD IMD and PMD then pivot by Usage with no aggregation

 

[sandy666]

if this is your source data (blue table)

Location_Usage	Date	BMD	IMD	PMD		Location_Usage	Attribute	January	February
Home	January	50​	100​	100​		Home	BMD	50​	75​
Home	February	75​	80​	80​		Home	IMD	100​	80​
						Home	PMD	100​	80​

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location_Usage", type text}, {"Date", type text}, {"BMD", Int64.Type}, {"IMD", Int64.Type}, {"PMD", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date", "Location_Usage"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Date]), "Date", "Value", List.Sum)
in
    #"Pivoted Column"[/SIZE]

 

[peter789]

Deleted