cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
justlogmein
Helper II
Helper II

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

 

 

 

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
AlexisOlson
Super User
Super User

@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
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Works perfectly, many thanks Pat!

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!