Count if cell contains text (Across more columns)

[mysho999]

Hello,
I would need to calculate for each row in the table how many times the word "ÁNO", "NIE", ...
But the joke is that I need to count it not for the entire row but only for the selected columns in that row.

Thank you very much

Table:

[v-diye-msft · Accepted solution]

Hi @mysho999 ,

I created a sample like this, and we’d like to calculate the counts of “ANO” & ”NIE” in selected columns for each row.

Attached pbix here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Ec6dsXHB20VMvdBdwazyAA...

We should have the slicer for selecting different columns, thus we do below steps in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTzV9KBkn6erlAyVicaRQyhCiSDLoYugywPMw1TBqbHEch2AmJnIHZRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Transposed Table" = Table.Transpose(#"Added Index"),
    #"Reversed Rows" = Table.ReverseRows(#"Transposed Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"1", type text}, {"2", type text}, {"3", type text}, {"4", type text}, {"5", type text}, {"6", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"6"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"

Please note that A,B,C,D are the columns name, and 1,2,3,4,5 are the Rows index

Applied the change, add the slicer of columns and rows, and use the measures:

Times of ANO = CALCULATE(COUNTROWS(Table2),FILTER(Table2,[Value]="ANO"))
Times of NIE = CALCULATE(COUNTROWS(Table2),FILTER(Table2,[Value]="NIE"))

Best regards,

Dina Ye

[v-diye-msft · Accepted solution]

Hi @mysho999 ,

I created a sample like this, and we’d like to calculate the counts of “ANO” & ”NIE” in selected columns for each row.

Attached pbix here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Ec6dsXHB20VMvdBdwazyAA...

We should have the slicer for selecting different columns, thus we do below steps in query editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTzV9KBkn6erlAyVicaRQyhCiSDLoYugywPMw1TBqbHEch2AmJnIHZRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Transposed Table" = Table.Transpose(#"Added Index"),
    #"Reversed Rows" = Table.ReverseRows(#"Transposed Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"1", type text}, {"2", type text}, {"3", type text}, {"4", type text}, {"5", type text}, {"6", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"6"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"

Please note that A,B,C,D are the columns name, and 1,2,3,4,5 are the Rows index

Applied the change, add the slicer of columns and rows, and use the measures:

Times of ANO = CALCULATE(COUNTROWS(Table2),FILTER(Table2,[Value]="ANO"))
Times of NIE = CALCULATE(COUNTROWS(Table2),FILTER(Table2,[Value]="NIE"))

Best regards,

Dina Ye

[v-diye-msft · Accepted solution]

Hi @mysho999 ,

If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!

Best regards,

Dina Ye