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.
I have a table with about 50 columns with rows that either have True or False in them. Is it possible to count the number of times either true or false appears in each column without going through each column one by one?
A | B | C |
True | False | True |
False | False | True |
A | B | C | |
True | 1 | 0 | 2 |
False | 1 | 2 | 0 |
So I have something like the first table and im trying to get something like the 2nd table where it shows the number of true/false that exist in each column.
Hi @JMM414141 ,
This is a Power Query solution, create a blank query and past this m code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCikqTVXSUXJLzCkG0WBurE40XABVIhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type logical}, {"B", type logical}, {"C", type logical}}),
Table = Table.Combine({ Table.AddColumn(#"Changed Type", "Type", each "True"), Table.AddColumn(#"Changed Type", "Type", each "False") }),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Table, {"Type"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if Logical.FromText([Type]) = [Value] then 1 else 0),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Custom", List.Sum),
#"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Type", type logical}, {"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}})
in
#"Changed Type1"
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 |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |