Can a value from another table be retrieved if it is contained in the current table with M query?

[Anonymous]

I have two tables and need to retreive an ID from Table2 if its value is contained in a column in Table1. This is an example of Table1:

Description

Snickers Bar

Beverage

Gift Card

Here is an example from Table2

ID       Indicator

1         Snickers

2         Gift Card

3         Bar

I need to use M query in the Query Editor to say "IF Table1[Desc] contains Table2[Indicator] then retrieve Table2[ID]"

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may check below formula.Attached sample file for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7LTM5OLSpWcEosUorViVZySi1LLUpMTwVz3DPTShScE4tSlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each (let currentDescription = [Description] in Table.SelectRows(Table2, each Text.Contains (currentDescription,[Indicator] ))){0}[ID]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom1", {{"Custom.1", null}})
in
    #"Replaced Errors"

Regards,

[Ashish_Mathur · Accepted solution]

Hi,

You can use Fuzzy Lookup (April 2019 release of PowerBI desktop).  In the first screenshot, there an additional option that is not visible i.e. Number of records - set that to 1.

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may check below formula.Attached sample file for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7LTM5OLSpWcEosUorViVZySi1LLUpMTwVz3DPTShScE4tSlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each (let currentDescription = [Description] in Table.SelectRows(Table2, each Text.Contains (currentDescription,[Indicator] ))){0}[ID]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom1", {{"Custom.1", null}})
in
    #"Replaced Errors"

Regards,

[Anonymous]

@v-cherch-msft, this is great. Really works well. Is there a way I could have all of the matching IDs that are returned concatenated into a single string separated by a semicolon, as in "1;2;3;5"

Currently, I am only getting one row for matching IDs based on sort order like a VLOOKUP.