Search (Text Contain) multiple values

[shuyin]

Hi,

I want to perform the search of each row in column A for all the value in column B. How can do that in PQ?

example as below:

Text	Key	Result
A B C	R	B
A R D	B	R
A G B	G	G

Thanks

 

[shuyin]

please use this example:

Text	Key	Result
A B C	R	B
A R D	B	R
A G E	G	G

 

[MarcelBeug]

This will do. In case the Text has multiple Key values, you will get them all, separated by ", ".

let
    Source = Table1,
    Result = Table.AddColumn(Source, "Result", (This) => Text.Combine(List.Select(Source[Key], each Text.Contains(This[Text], _)),", "))
in
    Result

 

[shuyin]

that works like a charm .

Could you help to explain the uses of (This) => in the query.

Thanks,

 

[MarcelBeug]

Yes.

Typicallly, when adding a column to a table, instead of (This) =>, the keyword each is generated and you can refer to other columns with [column name].

In this case we need to refer to [Text] within the nested List.Select, and any references within the List.Select are by default references to elements in the list; [Text] would be an unknown element in the list.
So, the underscore _ in the formula refers to a list element and not to a record in the table.

In order to allow references to [Text] within the nested function, a custom function is used instead of each.
The function is declared with a name of a parameter between parentheses followed by =>.
That parameter contains the current table record. I used "This" as the parameter name so you will get This[Text] which is quite readable.
Technically I could have chose any valid parameter name.

So basically the parameter name is the vehicle used to allow references to the table columns within the nested List.Select.

 

[shuyin]

Thank you for your explanation. I've tried to work around the reusable function approach as below:

[FONT=Helvetica-Normal]let[/FONT][FONT=Helvetica-Normal]    Source = Table1,[/FONT]
[FONT=Helvetica-Normal]    fxc = (This as table, s)=> Text.Combine(List.Select(Source[Key], each Text.Contains(This{s}[Text], _)),", "),[/FONT]
[FONT=Helvetica-Normal]    Result = Table.AddColumn(Source, "Result", each fxc(Source,_))[/FONT]
[FONT=Helvetica-Normal]in[/FONT]
[FONT=Helvetica-Normal]    Result[/FONT]

it requires a parameter "s" so that Text.Contains can extract the value of each element in [Text] column.

Could you help to explain bit further why your code didn't require that parameter but still work as well?
In Text.Contains description, it works with a value string, not with a list:

Text.Contains(string as nullable text, substring as text, optional comparer as nullable function) as nullable logical

 

[MarcelBeug]

This would be my version of a reusable function fxc (as a separate query):

(keys as list, s as text) as text => Text.Combine(List.Select(keys, each Text.Contains(s, _)),", ")

And the query:

let
    Source = Table1,
    Result = Table.AddColumn(Source, "Result", each fxc(Source[Key],[Text]))
in
    Result

So instead of using a parameter of type table and hard coded column [Text] in the function, my function takes a column as parameter.
Your parameter s is in fact a rrecord, as you call your fxc function with _ as second parameter, which is the current record of the Source table.
Note that Table.AddColumn works row by row, so "each" in this context means the current record of the table.
In fact your parameter "s" was my original parameter "This", so the question is not why I didn't use that parameter, but why I didn't use the other parameter.
The answer to that question is: I hardcoded "Source" in my function.

Actually I was surprised that your code actually works, because of the construction: This{s}[Text]
As "This" is a table and "s" is a record, that construction means something like: the Text in record "s" of table "This".
In this part, "s" is already the record from table "This", so you don't need "This", in other words you can replace This{s}[Text] with s[Text]