Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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!!
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.
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!!
Check out the July 2025 Power BI update to learn about new features.