Count same value in some columns

[Anonymous]

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         No              No

No         Yes              Yes

anyone knows how to create the formula to calculate all "yes" values in just one measure??

[Payeras_BI · Accepted solution]

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"

[CNENFRNL]

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"

[Payeras_BI · Accepted solution]

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"

[Anonymous]

but, if i have about 50 columns of metadata and i want to count the number of yes in all of them, the upivot step will be a nightmare,

[Payeras_BI]

Hi @Anonymous ,

Could you provide some sample data?

[Anonymous]

i have added an example table above.

A           B                C             D

n/a        Yes            n/a          n/a

No        n/a              No         n/a

No         Yes              Yes       n/a

I nned to count the number of n/a of a table, like the exmpale below but imagine in 50 colummns

and i need a formula to count them straigh away to the main table, without any traspose or similar

thanks

[Payeras_BI · Accepted solution]

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"

[Anonymous]

anyone can help?