Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
ddpl
Solution Sage
Solution Sage

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
1 ACCEPTED SOLUTION
shafiz_p
Super User
Super User

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

View solution in original post

3 REPLIES 3
ddpl
Solution Sage
Solution Sage

@shafiz_p , @Anonymous Thanks for the reply folks

Anonymous
Not applicable

Thanks for you reply, @shafiz_p .

 

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

1.Add a custom column with table TabFull.

vstephenmsft_1-1722827688917.png

vstephenmsft_2-1722827731969.png

 

2.Expand it.

vstephenmsft_3-1722827798574.png

vstephenmsft_4-1722827813330.png

 

3.Add a custom column to compare them.

vstephenmsft_5-1722827964092.png

vstephenmsft_6-1722827983976.png

 

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

vstephenmsft_7-1722827999715.png

 

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

vstephenmsft_8-1722828109110.png

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

vstephenmsft_9-1722828119706.png

vstephenmsft_11-1722828221765.png

 

Merge query and expand it:

vstephenmsft_10-1722828198376.png

vstephenmsft_12-1722828235083.png

vstephenmsft_13-1722828254892.png

vstephenmsft_14-1722828264908.png

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
Super User
Super User

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.