Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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      ...
Need Help
  • Payeras_BI's avatar
    Payeras_BI
    5 years ago

    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"

     

  • Payeras_BI's avatar
    Payeras_BI
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Payeras_BI
Solution Sage
4 years ago

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"