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

No         Yes              Yes

 

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

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"

     

7 Replies

  • Payeras_BI's avatar
    Payeras_BI
    Solution Sage
    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"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        anyone can help?

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago
    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"