Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
justlogmein
Helper III
Helper III

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

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

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors