How to a COUNTIFS equivalent but use Text.Contains rather than =

[justlogmein]

I have a list that has a column containing comma seperated numbers. I have worked out a way (well copied a way from someone else) to count how many rows have the same value AND part ID as the current one. However, what I want to do is, instead of searching for the entire comma seperated text value, is to search for the first number within that value. So essentially split it, and then do a search for that first split value WITHIN the same column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKjE0NlfSUTI0MdIxNjZRitWBiVoARY0MTXTMjE11jM0NdMwNzJFkLbHqGTVp1CS4SdhNwdQ9NHxDM5NAomhaaWY7fnvQLBj0ivGHAmG9aFyQMWAjYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PARTID = _t, #"Matching Response Path" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PARTID", type text}, {"Matching Response Path", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"PARTID", Order.Ascending}}),
    Custom1 = Table.AddColumn(#"Sorted Rows", "Count", each Table.RowCount(Table.SelectRows(#"Sorted Rows",(R) => _[Matching Response Path] = R[Matching Response Path] and _[PARTID] = R[PARTID])), type number)
in
    Custom1

[AlexisOlson]

@mahoneypat has a good solution but you can also do this with almost no need to write M yourself by using Group By and Merge Queries.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKjE0NlfSUTI0MdIxNjZRitWBiVoARY0MTXTMjE11jM0NdMwNzJFkLbHqGTVp1CS4SdhNwdQ9NHxDM5NAomhaaWY7fnvQLBj0ivGHAmG9aFyQMWAjYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PARTID = _t, #"Matching Response Path" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PARTID", type text}, {"Matching Response Path", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"PARTID", Order.Ascending}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Sorted Rows", "Text Before Delimiter", each Text.BeforeDelimiter([Matching Response Path], ","), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Text Before Delimiter", {"PARTID", "Text Before Delimiter"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Text Before Delimiter", {"PARTID", "Text Before Delimiter"}, #"Grouped Rows", {"PARTID", "Text Before Delimiter"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"})
in
    #"Expanded Grouped Rows"

I think this has better computational efficiency too.

[mahoneypat · Accepted solution]

You can use a custom column with this expression to get your desired result.

= let
thispart = [PARTID],
thisfirst = Text.Split([Matching Response Path], ","){0},
firstlist = List.Transform(Table.SelectRows(#"Sorted Rows", each [PARTID] = thispart)[Matching Response Path], each Text.Split(_, ","){0}),
result = List.Count(List.Select(firstlist, each _ = thisfirst))
in
result

And thank you for pasting your data as M code! Makes it much easier to help. Here is the full query back.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WCkgsKjE0NlfSUTI0MdIxNjZRitWBiVoARY0MTXTMjE11jM0NdMwNzJFkLbHqGTVp1CS4SdhNwdQ9NHxDM5NAomhaaWY7fnvQLBj0ivGHAmG9aFyQMWAjYgE=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [PARTID = _t, #"Matching Response Path" = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    Source,
    {{"PARTID", type text}, {"Matching Response Path", type text}}
  ),
  #"Sorted Rows" = Table.Sort(#"Changed Type", {{"PARTID", Order.Ascending}}),
  Custom1 = Table.AddColumn(
    #"Sorted Rows",
    "Count",
    each Table.RowCount(
      Table.SelectRows(
        #"Sorted Rows",
        (R) => _[Matching Response Path] = R[Matching Response Path] and _[PARTID] = R[PARTID]
      )
    ),
    type number
  ),
  #"Added Custom" = Table.AddColumn(
    Custom1,
    "Custom",
    each
      let
        thispart = [PARTID],
        thisfirst = Text.Split([Matching Response Path], ","){0},
        firstlist = List.Transform(
          Table.SelectRows(#"Sorted Rows", each [PARTID] = thispart)[Matching Response Path],
          each Text.Split(_, ","){0}
        ),
        result = List.Count(List.Select(firstlist, each _ = thisfirst))
      in
        result
  )
in
  #"Added Custom"

Pat

[justlogmein]

Works perfectly, many thanks Pat!