cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Add Column based on other table columns value

Hi!

Need your help with simple task..

 

I have 2 tables with no relations between them:

#Table1({"ColumnA", "ColumnB"}, {{1,Green Apple },{2,Red Potato},{3,Blue Star},{4,Kung Fu Panda}})  

#Table2({"ColumnC", "ColumnD"}, {{apple, fruit},{potato, vegetable},{star, astronomical object},{kung fu, martial art}}) 

 

The goal is to add ColumnE to Table1 with this condition:

if Text.Contains(Table1[ColumnB, Table2[ColumnC]) then Table2[ColumnD] else null 

 

The result should be:

#Table1({"ColumnA", "ColumnB","ColumnE"}, {{1,Green Apple , fruit},{2,Red Potato, vegetable},{3,Blue Star, astronomical object},{4,Kung Fu Panda, martial art}})  

 

This perfectly works with manual static if blocks, but I have no idea how to automate this task using values from table.

 

Any ideas?

 

1 ACCEPTED SOLUTION

Accepted Solutions
New Member
6 REPLIES 6
Super User IV
Super User IV

@Bublic 

@ImkeF @edhans 

 

In DAX, this would be LOOKUPVALUE or MAXX(FILTER(...)...)


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

@ 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!




Super User II
Super User II

Hi @Bublic ,

 

Try splitting your "Column B" on spaces. So you will send up with somwhting like this:

Column B.1 Column B.2

Green          Apple

Red             Potato.....

 

Now create relationship between both of your table using Column B.2  from 1st table and Column C from 2nd table.

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Column B can contain value like : "The biggest green apple all over the world". How can I guess which splitted column to use?

Hi @Bublic ,

 

In that case, you shared the wrong sample data to your issue.

Go with the option suggested by @Greg_Deckler .

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




In my casev  LOOKUPVALUE is not working without relationship(

New Member

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors