Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Croy
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

Please try my custom visuals

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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]) 

 

 

Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Please try my custom visuals

Hi Zubair, if the text field contains multiple keywords the formula returns multiple categories. Anyway to just get the first one that matches.

Awesome solution Zubair. Many thanks 🙂

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.