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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Text search

Hi everyone,

 

I need to search text Table[Column] within text Table2[Column] and return text value of Table2[Column].

How to make the measure?

Please Help!

1 ACCEPTED SOLUTION

Hello there @Ara_Karapetyan ! I think it would be best for you to perform these actions in Power Query rather than a measure. Open the Query Editor and add a custom column. In the pop up window paste the following code.

Try this for the category column:

 

=let myvalue=[Search string]
in
Text.Combine(
    Table.SelectRows(SecondTable,
each Text.Contains(myvalue,[Name]))[Category]
,
",")

 

 And this for the group:

 

=let myvalue=[Search string]
in
Text.Combine(
    Table.SelectRows(SecondTable,
each Text.Contains(myvalue,[Name]))[Group]
,
",")

 

  

goncalogeraldes_0-1631527212174.png

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

View solution in original post

8 REPLIES 8
v-yalanwu-msft
Community Support
Community Support

Hi, @Ara_Karapetyan ;

According to my understand, you could create a measure .

flag = VAR _A=SUMMARIZE('Table',[Column])
RETURN IF(MAX([Column]) IN _A,1,0)

Then apply it into filter.

vyalanwumsft_0-1631513635092.png

The final output is shown below:

vyalanwumsft_1-1631513754476.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

goncalogeraldes
Super User
Super User

@Ara_Karapetyan  Please provide more information or a sample of what you need. Do you need a LOOKUPVALUE()? An IF() statement? A SWITCH()?

 

Please refer to this article on how to post your answers in the best way

I am not sure.

I use two tables: one with transactions, another one is helper table with additional info. I need to find a part of text value from the helper table within transaction name text column in transation table and get all the corresponding fields from the helper.

 

Thank you in advance!

@Ara_Karapetyan Depending on wether you have a relationship between both tables you can either use the RELATED() function or the LOOKUPVALUE(). Can you provide some sample data please?

I use 2 tables:

The main one contains thousands of transactions like:

DateName
21/08/21LENTA-809
23/08/21LENTA-809
24/08/21BELAYA APTEKA ORDZH
25/08/21MEDKLUB
26/08/21STOLINYJ DOKTOR

 

The second one (the helper table) contains the list of search strings, categories, and groups:

Search stringCategoryGroup
apteDrugstoreHealth
lentaSuipermarketFood
docDrugstoreHealth

 

My need is to filter the upper table "Name" column with the "Search strig" from the second one and use "Category" and "Group" columns.

I cant use relations between the tables because I need sort of "CONTAINSSTRING" solution.

Thanks in advance.

Hello there @Ara_Karapetyan ! I think it would be best for you to perform these actions in Power Query rather than a measure. Open the Query Editor and add a custom column. In the pop up window paste the following code.

Try this for the category column:

 

=let myvalue=[Search string]
in
Text.Combine(
    Table.SelectRows(SecondTable,
each Text.Contains(myvalue,[Name]))[Category]
,
",")

 

 And this for the group:

 

=let myvalue=[Search string]
in
Text.Combine(
    Table.SelectRows(SecondTable,
each Text.Contains(myvalue,[Name]))[Group]
,
",")

 

  

goncalogeraldes_0-1631527212174.png

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

It works for me!! thanks a lot to share it!!

So there is no way to do it via measure?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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