Forum Discussion
Count True/False values in multiple columns
| 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"