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

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

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.

     

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    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.