Count cells that contain a specific character

[Anonymous]

Hi,

I'm using this formula in Power Query to create a new column based on the cell values:

= Table.AddColumn(#"Changed Type2", "CompletedActual", each Text.Length(Text.Combine(List.Transform(Record.FieldValues(_),each if _="✔" then "1" else ""))))

This creates a new column with the count of ✔ for each row but I also want to include in this count the cells that contain "!". 

Any help/tip is welcome. If you need more information/clarification tell me.

Thank you,
Ferk

[dufoq3]

Hi @Anonymous,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JcixEcAwDELRVYTqbKRzAfJILj1dJgmnNNz7VCX53kPmeiolOYQJC/BMRFgRgcFcVO8flLqB7Vwf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Ad_CountSeparetely = Table.AddColumn(Source, "Count Separetely", each "Count ✔ = " & Text.From(List.Count(List.Select(Text.ToList([Column1]), each _ = "✔")))
& ", " &
"Count ! = " & Text.From(List.Count(List.Select(Text.ToList([Column1]), each _ = "!"))), type text),
    Ad_CountTogether = Table.AddColumn(Ad_CountSeparetely, "Count Together", each List.Count(List.Select(Text.ToList([Column1]), each List.Contains({"✔","!"}, _))), Int64.Type)
in
    Ad_CountTogether

[Anonymous]

Hi @dufoq3 , thank you for your help. 

Instead of specifying Column1 can you rewrite to iterate all the columns on the table and you can simplify to get only the Count Together?

Thanks,
Ferk

[dufoq3]

@Anonymous of course 🙂

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYsxDoAwCEWvAswdak08ikvtACUm7dp4E0dP50mkJG6P9z45E/P73MwUqDUjKiGTiBgKmkQwAh2we7EDcc4CDT07dLfgIwB08C+2DOiVpaq5OTmuGNP2exapFVFnXdI6dfkA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Merged = Table.FromList(Table.ToList(Source, Combiner.CombineTextByDelimiter("")), Splitter.SplitByNothing(), type table [Merged=text]),
    Ad_CountTogether = Table.AddColumn(Merged, "Count ✔ and !", each List.Count(List.Select(Text.ToList([Merged]), each List.Contains({"✔","!"}, _))), Int64.Type)
in
    Ad_CountTogether