Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Perfect. Thank you!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |