cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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.

1 ACCEPTED SOLUTION

Hi @Croy

 

Try this calculated column in DETAILS table

 

Calculated Column =
CONCATENATEX (
    FILTER ( Products, SEARCH ( Products[Products], Details[Text],, 0 ) > 0 ),
    Products[Products],
    ", "
)
Regards,
Zubair


View solution in original post

5 REPLIES 5
Advocate I
Advocate I

Hi Croy, if you link both tables using "Product" as the relationship point you should be able to create a new column on your Details Table using  = RELATED(Product[Product]) 

 

 

Super User IV
Super User IV

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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Croy

 

Try this calculated column in DETAILS table

 

Calculated Column =
CONCATENATEX (
    FILTER ( Products, SEARCH ( Products[Products], Details[Text],, 0 ) > 0 ),
    Products[Products],
    ", "
)
Regards,
Zubair


View solution in original post

Awesome solution Zubair. Many thanks 🙂

@Croy this formual by zubai should give the result as u suggested

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors