Unpivot related columns

Hi Anonymous

1. select "Audio" and "Video" columns, and select "Unpivot only selected columns",

2. after step1, select "Audio Poor" and "Video Poor" columns, and select "Unpivot only selected columns",

3.add a custom column

Text.Contains([Attribute.1],[Attribute])

4. keep rows which meets your requirement

 

Code in Advanced editor

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\7\7.31\7.31.xlsx"), null, true),
    Sheet3_Sheet = Source{[Item="Sheet3",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet3_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"month", type text}, {"Audio", Int64.Type}, {"Video", Int64.Type}, {"Audio Poor", Int64.Type}, {"Video Poor", Int64.Type}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Audio", "Video"}, "Attribute", "Value"),
    #"Unpivoted Only Selected Columns1" = Table.Unpivot(#"Unpivoted Only Selected Columns", {"Audio Poor", "Video Poor"}, "Attribute.1", "Value.1"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns1", "Custom", each Text.Contains([Attribute.1],[Attribute])),
    keeprows=Table.SelectRows(#"Added Custom",each[Custom]=true)
in
    keeprows

Best Regards

Maggie