Forum Discussion

ddpl's avatar
ddpl
Solution Sage
2 years ago
Solved

String and Sub-String Filter

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 fro...
Need Help
Show and Tell
Tips and Tricks
  • shafiz_p's avatar
    shafiz_p
    2 years ago

    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!!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.

 

 

sample51125.pbix32 KB