Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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]"
Solved! Go to 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,
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.
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.
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,
@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.
User | Count |
---|---|
87 | |
72 | |
69 | |
64 | |
55 |
User | Count |
---|---|
99 | |
91 | |
80 | |
74 | |
64 |