Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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
This is what I looked for: https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/
Thanks to @amitchandak
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
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
In DAX, this would be LOOKUPVALUE or MAXX(FILTER(...)...)
In my casev LOOKUPVALUE is not working without relationship(
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |