Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Count cells that contain a specific character

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

Need Help

3 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

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

      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's avatar
        dufoq3
        Community Champion
        2 years ago

        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