Forum Discussion

justlogmein's avatar
justlogmein
Icon for Helper III rankHelper III
4 years ago
Solved

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

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

 

 

 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

3 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    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