Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Unpivoting related columns

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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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.