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

Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM. Register now.

Reply
MP-iCONN
Resolver I
Resolver I

Search a column for a certain string and return true or false

I have two columns: "Component ID" and "Where Used." The "Where Used" column lists all Parent IDs associated with each Component ID.

 

How can I search the "Where Used" column to check if any entries don’t start with "X" and return false if that's the case?  In all instances there will be a space between each Parent ID in that Where Used column as seen in the table example below.

 

For example:

 

Component IDWhere UsedTrue/False

10B-1234

M1010 M2245 X1223False

10C-1456

M1111 M4543 X1232 Z1234False

11H-2132

X1234 X1223 X2132True

 

Screenshot of data in case formatting from PBI Community Forum is difficult to read:

MPiCONN_0-1727719897340.png

 

Thank you for any help/advice that you can give.

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Replace the space with a pipe  "|"  and use PATH functions.

 

lbendlin_0-1727722112020.png

 

TF = 
var p = SUBSTITUTE([Where Used]," ","|")
var g = ADDCOLUMNS(GENERATESERIES(1,PATHLENGTH(p)),"f",if(left(PATHITEM(p,[Value]),1)="X",0,1))
return sumx(g,[f])=0

 

or if you want it in Power Query 

lbendlin_0-1727722575471.png

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "JcwxDsAgDAPAr0SZQcJO6APapQs7AvH/bxTSjPY5cyrKnUFzTdpQUKSRXqWDNF3pgCfD6xVgnzSvbgcYZcQ0GN5MGDc7lf8fpEe21gc=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Component ID" = _t, #"Where Used" = _t]
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "T/F", 
    each List.Distinct(List.Transform(Text.Split([Where Used], " "), each Text.Start(_, 1))) = {"X"}
  )
in
  #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

 

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

Replace the space with a pipe  "|"  and use PATH functions.

 

lbendlin_0-1727722112020.png

 

TF = 
var p = SUBSTITUTE([Where Used]," ","|")
var g = ADDCOLUMNS(GENERATESERIES(1,PATHLENGTH(p)),"f",if(left(PATHITEM(p,[Value]),1)="X",0,1))
return sumx(g,[f])=0

 

or if you want it in Power Query 

lbendlin_0-1727722575471.png

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "JcwxDsAgDAPAr0SZQcJO6APapQs7AvH/bxTSjPY5cyrKnUFzTdpQUKSRXqWDNF3pgCfD6xVgnzSvbgcYZcQ0GN5MGDc7lf8fpEe21gc=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Component ID" = _t, #"Where Used" = _t]
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "T/F", 
    each List.Distinct(List.Transform(Text.Split([Where Used], " "), each Text.Start(_, 1))) = {"X"}
  )
in
  #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

 

This worked out great.  Thank you!

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors