Forum Discussion

MP-iCONN's avatar
MP-iCONN
Resolver 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 entri...
  • lbendlin's avatar
    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.