Add Column based on other table columns value

[Bublic]

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?

[Bublic · Accepted solution]

This is what I looked for: https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/

Thanks to @amitchandak

[Pragati11]

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

[Bublic]

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

[Pragati11]

Hi @Bublic ,

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

Go with the option suggested by @Greg_Deckler .

Thanks,

Pragati

[Greg_Deckler]

@Bublic 

@ImkeF @edhans 

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

[Bublic]

In my casev  LOOKUPVALUE is not working without relationship(