Forum Discussion
MP-iCONN
1 year agoResolver 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 entri...
- 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])=0or 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.
lbendlin
1 year agoSuper User
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
1 year agoResolver I
This worked out great. Thank you!