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)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Location_Usage[/td][td=bgcolor:#5B9BD5]Date[/td][td=bgcolor:#5B9BD5]BMD[/td][td=bgcolor:#5B9BD5]IMD[/td][td=bgcolor:#5B9BD5]PMD[/td][td][/td][td=bgcolor:#70AD47]Location_Usage[/td][td=bgcolor:#70AD47]Attribute[/td][td=bgcolor:#70AD47]January[/td][td=bgcolor:#70AD47]February[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Home[/td][td=bgcolor:#DDEBF7]January[/td][td=bgcolor:#DDEBF7]

50​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td=bgcolor:#DDEBF7]

100​

[/td][td][/td][td=bgcolor:#E2EFDA]Home[/td][td=bgcolor:#E2EFDA]BMD[/td][td=bgcolor:#E2EFDA]

50​

[/td][td=bgcolor:#E2EFDA]

75​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Home[/td][td]February[/td][td]

75​

[/td][td]

80​

[/td][td]

80​

[/td][td][/td][td]Home[/td][td]IMD[/td][td]

100​

[/td][td]

80​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Home[/td][td=bgcolor:#E2EFDA]PMD[/td][td=bgcolor:#E2EFDA]

100​

[/td][td=bgcolor:#E2EFDA]

80​

[/td][/tr]
[/table]

[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