Forum Discussion
Count same value in some columns
Hi all, i have some columns in my tables with Yes or No. I would like to calculate the amount of Yes and No of alll them A B C Yes Yes No No ...
Hi Anonymous,
In PQ I would Unpivot the columns, filter by "Yes" and, then, Count Values.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtKBkn75SrE60SBKB07ABRAKY2MB", 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 text}, {"B", type text}, {"C", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] = "Yes")), #"Calculated Count" = List.NonNullCount(#"Filtered Rows"[Value]) in #"Calculated Count"Hi Anonymous ,
Try this instead:
let Origen = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WytNPPLRA4dACJR2lyNRiMFMBJgCUg5KxOtFKfvkYUn75KPIQM+AkWCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]), #"Personalizada agregada" = Table.AddColumn(Origen, "# n/a", each List.Count(List.Select(Record.ToList(_), each _ = "n/a"))) in #"Personalizada agregada"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtKBkn75SrE60SBKB07ABRAKY2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
#"Count Yes" = List.Accumulate(Table.ToRows(Source), 0, (s,c) => s + List.Count(List.Select(c, each _ = "Yes")))
in
#"Count Yes"