Text search

[Ara_Karapetyan]

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!

[v-yalanwu-msft]

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.

The final output is shown below:

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]

@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

[Ara_Karapetyan]

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!

[goncalogeraldes]

@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?

[Ara_Karapetyan]

I use 2 tables:

The main one contains thousands of transactions like:

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

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

Search string	Category	Group
apte	Drugstore	Health
lenta	Suipermarket	Food
doc	Drugstore	Health

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.

[goncalogeraldes · 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]
,
",")

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

[SilviaM]

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

[Ara_Karapetyan]

So there is no way to do it via measure?