Forum Discussion
Count True/False values in multiple columns
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?
4 Replies
- Why not unpivot the data?
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"
