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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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 , @v-stephen-msft Thanks for the reply folks

v-stephen-msft
Community Support
Community Support

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors