cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vsyhr Frequent Visitor
Frequent Visitor

Grouping Yes/No Columns by a category

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 IDBathroom Extractor NeededBathroom Extractor ReceivedMould Kit NeededMould Kit Received
1Yes YesYesYes
2NoNoNoNo
3NoNoYesNo
4Yes NoYesNo

 

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. 

 

Crude Visualisation.png

Any help would be much appreciated, 

 

Thank you. 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Grouping Yes/No Columns by a category

@vsyhr 

 

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

 

image.png

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
HotChilli Established Member
Established Member

Re: Grouping Yes/No Columns by a category

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.

Super User
Super User

Re: Grouping Yes/No Columns by a category

@vsyhr 

 

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

 

image.png

Regards

 

Victor




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




vsyhr Frequent Visitor
Frequent Visitor

Re: Grouping Yes/No Columns by a category

Perfect. Thank you!