Find values from one table in the text of another table with the Query Editor

[Fabi]

Hi community, can you help me ?

I'm looking to find the words in one table in the text of another table.

I want a new column in the table "Table Text" that would contain all the "Keys" found in the "Text".
I tired the "Text.Contains" but I don't know how to replace "Sleep", "Eat", "Drink" by the column name "Keys" of the table "Table Keys" and I want all the "Keys" in the "Text", not the first "Keys" found :

So I tried with a formula found on the forum :
(let word = [Text Contains] in Table.SelectRows(#"Table Keys", each [Keys] = word)){0}[Keys]

But it doesn't work if I try to replace "[Text Contains]" by "[Text]".
I tired to combine this fomula with Text.Contains but I failed.

The result I'm looking for should look something like this :

I would just split the column with the corresponding separator then.
Thanks !

[Jimmy801 · Accepted solution]

Hello @Fabi 

I've prepared a solution for you. This query contains both tables in one query. This solution is quite specific and therefore not be rebuild using only the UI. Here the solution

let
	Keys = #table 
	(
		{"Keys"},
		{ {"Eat"}, {"Drink"}, {"Run"}, {"Sleep"} } 
	),
	Text = #table 
	(
		{"Text"},
		{ {"The dog run, sleep after."}, {"I eat coockies and drink milk"}, {"Run is healthy"}, {"Hello there !"} } 
	),
	AddList = Table.AddColumn 
	(
		Text,
		"Custom",
		each List.Select 
		(
			Keys[Keys 
		],
		(
			sel 
		)=>
		Text.Contains 
		(
			Text.Lower 
			(
				[Text] 
			),
			Text.Lower 
			(
				sel 
			)
		)
	)
	),
	Extracted = Table.TransformColumns 
	(
		AddList,
		{"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text} 
	)
 in 
	Extracted

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Fabi]

Thank @Jimmy801 ,

But I forgot to say that this is just an example in this post. In fact, I work with different tables, one that contains more than 200 keys and another that contains thousands of columns of text that will be more and more numerous with each update of the data. It would be very long and tiring to write all the keys and texts in the code.

[Jimmy801]

Hello @Fabi 

no, my solution is only to show you how to achieve your goal..

you can copy paste it to the editor and checking how it works and if this fits your needs

This said you have to use my query and implement in your environment, replacing my tables with yours (the keys with an external one, the other one with the table output of your main query)

Bye

Jimmy

[Fabi]

It works !
For beginners like me who would fall on this post : I just replaced

let
	Keys = #table 
	(
		{"Keys"},
		{ {"Eat"}, {"Drink"}, {"Run"}, {"Sleep"} } 
	),
	Text = #table 
	(
		{"Text"},
		{ {"The dog run, sleep after."}, {"I eat coockies and drink milk"}, {"Run is healthy"}, {"Hello there !"} } 
	),

by

let
	Keys = #"Table Keys",
	Text = #"Table Text",

Sorry for not understanding the first time, I have been working with Power BI for 3 weeks, I do not have everything in mind yet.
Thank you so much @Jimmy801 .

[Jimmy801]

Hello @Fabi 

i appreciate your feedback. 

I'm glad I was able to help

All the best

Jimmy