Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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(
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |