String and Sub-String Filter

[ddpl]

I have 2 Tables

TabSub

sub

iron

thanos

spider

TabFull

full

ironman

superman

spiderman

I want to lookup in full-string based on sub-string and get value in fron of sub-string in TabSub table

TabSub

sub	full
iron	ironman
thanos
spider	spiderman

[ddpl]

@shafiz_p , @v-stephen-msft Thanks for the reply folks

[v-stephen-msft]

Thanks for you reply, @shafiz_p .

Hi @ddpl , here's my solution you could try.

1.Add a custom column with table TabFull.

2.Expand it.

3.Add a custom column to compare them.

4.After filtering out false and below is the result.

If you wan to keep 'thanos', you can duplicate a table and merge them. 

In the duplicated table, go to applied steps, remove the steps to the original.

Merge query and expand it:

Such a step-by-step operation, I hope it is convenient for you to understand.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shafiz_p · Accepted solution]

Hi @ddpl Please use below highlighted steps in TabSub table power query to findout full string based on sub string.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WyizKz1OK1YlWKslIzMsvBjOLCzJTUouUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [sub = _t]),
TypeChanged = Table.TransformColumnTypes(Source,{{"sub", type text}}),

fullname = Table.AddColumn(
TypeChanged,
"FullName",
each
(
try
let
sub = [sub],
filterrows = Table.SelectRows(TabFull, each Text.Contains([full], sub))
in
filterrows{0}[full]

otherwise null
),
type text
)
in
fullname

Hope this helps!!

If this solved your problem, please mark it as a solution!!