Forum Discussion
Add column based on a list from another table
I have 2 tables (which are obtained via two separate queries):
- In Details table I have columns like Date, Country, Customer, Text and some other columns
- In Products table I have a list of products (in a column)
The Text column from Details table contains something like this: "Info request on product Delta" or "Inquiry about Gamma issues". Delta and Gamma are products listed in the Products table.
What I want is to add a Product column in Details table based on what the Text column contains and matching it with the Products table.
Any idea how to do this? Can I do it with power query M language? Or do I need DAX?
I want to slice and dice my Products based on Date, Country, Customer etc from Details event though my Products are buried in the Text column.
Hi Croy
Try this calculated column in DETAILS table
Calculated Column = CONCATENATEX ( FILTER ( Products, SEARCH ( Products[Products], Details[Text],, 0 ) > 0 ), Products[Products], ", " )
6 Replies
I'm thinking you will want to use the DAX SEARCH or FIND functions to achieve what you are going after. Perhaps coupled with LOOKUPVALUE as well. Now that I am thinking about it, I don't generally reference other queries from a single query in M in the manner you are describing.
