Forum Discussion

MP-iCONN's avatar
MP-iCONN
Icon for Resolver I rankResolver I
1 year ago
Solved

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:

 

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

Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    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.

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    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.