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(Re...
Need Help
dufoq3's avatar
dufoq3
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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