Grouping Yes/No Columns by a category

[Anonymous]

Hi there, 

I'm fairly new to PowerBi and I'm hoping someone could help me with creating a particular visualisation if possible. A sample of my data is below. There's approximately 5000 rows with columns detailing 10 particular housing interventions. 

Case ID	Bathroom Extractor Needed	Bathroom Extractor Received	Mould Kit Needed	Mould Kit Received
1	Yes	Yes	Yes	Yes
2	No	No	No	No
3	No	No	Yes	No
4	Yes	No	Yes	No

I'm looking at creating a visualisation as below. Where I have a count of the number of "Yes" responses in each column at categorize these by intervention type, i.e bathroom extractor or mould kit. Please excuse the crude drawing. 

Any help would be much appreciated, 

Thank you. 

[Vvelarde · Accepted solution]

@Anonymous 

Hi, you can edit your structure in Power Query.

Paste this in your Advanced Editor 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUYpMLT60AEKjkLE60UpGQLZfPjoBkjFGEYTogkqZIBmKJhsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case ID" = _t, #"Bathroom Extractor Needed" = _t, #"Bathroom Extractor Received" = _t, #"Mould Kit Needed" = _t, #"Mould Kit Received" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case ID", Int64.Type}, {"Bathroom Extractor Needed", type text}, {"Bathroom Extractor Received", type text}, {"Mould Kit Needed", type text}, {"Mould Kit Received", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Case ID"}, "Attribute", "Value"),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Unpivoted Columns", "Text Before Delimiter", each Text.BeforeDelimiter([Attribute], " Needed"), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Before Delimiter",{{"Text Before Delimiter", "Category"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns"," Received","",Replacer.ReplaceText,{"Category"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "Intervention Type", each if Text.Contains([Attribute], "Needed") then "Intervention Needed" else "Intervention Received"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Attribute"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Value", "Response"}}),
    #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns1",{{"Response", Text.Trim, type text}, {"Category", Text.Trim, type text}, {"Intervention Type", Text.Trim, type text}})
in
    #"Trimmed Text"

Close & Apply

And create a measure:

CountYes =
CALCULATE (
    COUNT ( Table1[Response] ),
    FILTER ( Table1, Table1[Response] = "Yes" )
)

Finally use a column chart

Regards

Victor

[Anonymous]

Perfect. Thank you!

[HotChilli]

I would reshape the data first in Power Query editor.

Select all the columns apart from 'Case ID' and choose Unpivot Columns in the Transform tab.

This will give 3 columns of Case ID, Attribute, Value.

Close and Apply Power Query editor.

Then write a measure like

CountYes = 
CALCULATE ( COUNTROWS ( Table1 ), Table1[Value] = "Yes" )

Then on a clustered column chart , put Attribute on the axis and CountYes on the value.