Unpivoting related columns

[Anonymous]

Thanks for wanting to help! It should be a simple one....

I currently have a table that looks like this:

	Audio	Video	Audio Poor	Video Poor
May	10	8	4	2
June	12	7	2	1

And I'm looking for Power Query to give me this:

	Type	Count	Poor Count
May	Audio	10	4
May	Video	8	2
June	Audio	12	2
June	Video	7	1

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"Audio", Int64.Type}, {"Video", Int64.Type}, {"Audio Poor", Int64.Type}, {"Video Poor", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Month"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Text.Contains([Attribute],"Poor") = true then "Poor count" else "Count"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," Poor","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Custom]), "Custom", "Value"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Month", Order.Descending}})
in
    #"Sorted Rows"

Hope this helps.

[parry2k]

Solution attached.