Searching text in a column for text in another column in a different table in M

[murrayb8]

Hi, Im trying to search values in one column for values in another column in another table and then returning a corresponding column value.

Lets say I have two tables, A and B.

A contains the values I want to search, there could potentially be hundreds of thousands of values in here.

B contains a list of values I want to search for and another corresponding value column, there will most likely be hundreds of values in here.

Here is an example of these tables:

Example Table A:

Values to be searched

Fast cars

toy blue cars

remote control cars

Red to car

blue cars

green cars

other cars

great red cars

Example Table B:

Value to search for	Corresponding value
red car	Red
green car	Green
blue car	Blue

I essentially want to add a column in table A in query editor so that the output looks like this:

Values to be searched	Matched Value
Fast cars
toy blue cars	Blue
remote control cars
Red toy car
blue cars	Blue
green cars	Green
other cars
great red cars	Red

And as it could be working across hundreds of thousands of values to be matched I want to make it as efficient as possible.

Can anyone point me in the right direction here please?

Thanks in advance!

[Zubair_Muhammad · Accepted solution]

@murrayb8 

Here is a custom column formula. it works with sample data

See file attached as well

=let myvalue=[Values to be searched]
in
Text.Combine(
    Table.SelectRows(TableB,
each Text.Contains(myvalue,[Value to search for]))[Corresponding value]
,
",")

[Anonymous]

Hello @Zubair_Muhammad ,

I am trying similar but for a single column output, Would be great, if you could help me out. Here is the snapshot of my query.

Thanks

[Anonymous]

@Zubair_Muhammad 

appreciate this is an old one but stumbled across your code and it's so close to something I'm trying to achieve so hoped you may be able to help.

I'm searching Table 1 field [Description] for values in a column called [Tag] from a table called Symptom.

=let myvalue=[Description]
in
Text.Combine(
    Table.SelectRows(Symptom,
each Text.Contains(myvalue,[Tag]))[Tag]
,
",")

I'm looking for something which does that, but only returns the first hit rather than all with comma separation.

For bonus appriciations my next step is to return the value in the second column of the Symptom table.... 

Table is simple 2 columns:

Tag             Output

Red             It's red

So if I was searching a decription field value of "Is it red or orange".  It would look for the value 'Red' from the tag field and return 'it's red' from the output field.  If that's can't be done in power query, no biggie I'll just do a lookup in DAX. 

[Ashish_Mathur]

Hi,

This is an old thread.  Share some data, describe the question and show the expected result.

[Anonymous]

@Zubair_Muhammad You are a lifesaver. This behaves just like a vlookup in Excel. I was driving myself crazy trying to figure this out.

Question: the way I did this was by merging the tables first and then expanding the relevant columns.... is that an unnecessary step? As long as the table is connected to within the same PBIX file can I just referance the column in the other table for the lookup?

Thank you and As Salaam Alaikum

[Anonymous]

@Anonymous check this out

[Zubair_Muhammad]

Hi @Anonymous 

Wa alaikumus salam,

Merge using  "user iterface of Power Query" is always the best way to proceed.

In above case merge was not possible. That's why we had to use a custom formula

[murrayb8]

Thanks Zubair, seems a bit nicer illustrated and compact. As mentioned though still super heavy but thats PBI I think so using other routes now

[Mariusz]

Hi @murrayb8,

Please see the M code below two tables. 

// search
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkpNUUhOLFLSUQpKTVGK1YlWSi9KTc2DirmD2GDRpJzSVKigE5CpFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Value to search for" = _t, #"Corresponding value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value to search for", type text}, {"Corresponding value", type text}}),
    #"Lowercased Text" = Table.TransformColumns(#"Changed Type",{{"Corresponding value", Text.Lower, type text}})
in
    #"Lowercased Text"

// table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VYy7DYAwDAVXsVIzBwNQ0EQUJnkiBWDJNgXbE0IRUd69T4xh5v2CkQutIANrKshhGWIY2ZwSqzVyuWmt1W4Uh3hlOV1l735CprddReP/alPg7CheoL+UnbQ+fGp5AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Values to be searched", "Values to be searched - Copy"),
    #"Lowercased Text" = Table.TransformColumns(#"Duplicated Column",{{"Values to be searched - Copy", Text.Lower, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Lowercased Text", "Values to be searched - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Values to be searched - Copy.1", "Values to be searched - Copy.2", "Values to be searched - Copy.3"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter", {"Values to be searched", "Index"}, "Attribute", "Matched Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"Matched Value"}, search, {"Corresponding value"}, "searchFor", JoinKind.Inner),
    #"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"Index", "Matched Value"}),
    #"Merged Queries1" = Table.NestedJoin(#"Added Index", {"Index"}, #"Removed Other Columns", {"Index"}, "Removed Other Columns", JoinKind.LeftOuter),
    #"Expanded Removed Other Columns" = Table.ExpandTableColumn(#"Merged Queries1", "Removed Other Columns", {"Matched Value"}, {"Matched Value"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Expanded Removed Other Columns",{"Values to be searched", "Matched Value"})
in
    #"Removed Other Columns1"

Hope this helps.
Mariusz

[murrayb8]

Thanks Mariusz, I had a go at this and I think the columns were mixed up in the merged query step. I got there in the end though but it toolk hours to refresh as the text searching seems to be super slow. Found a way to run the same through a VM though so will be going that route from now on!