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

[MP-iCONN]

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 ID	Where Used	True/False
10B-1234	M1010 M2245 X1223	False
10C-1456	M1111 M4543 X1232 Z1234	False
11H-2132	X1234 X1223 X2132	True

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

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

[lbendlin · Accepted solution]

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

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 

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.

[MP-iCONN]

This worked out great.  Thank you!