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 fron of sub-string in TabSub table

TabSub

subfull
ironironman
thanos 
spiderspiderman
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!!

3 Replies

  • shafiz_p's avatar
    shafiz_p
    Super User
    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.