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

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