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.
Hey PBi Users,
Need a quick help with something.
So i have two tables in my project and I need to copy the value of two columns from one table to another table based on two keys.
and finally my table 1 should look like the pic below
Now the thing to note here is that the rows of both the tables are uniquely identitfied by the combination of 'ARD Key' and 'Material Key'.
If someone can provide me a way to do it, I would be really very grateful. 🙂
Solved! Go to Solution.
Without seeing the data I can't tell you what the problem is. My formula works against your expample data.
Changed = LOOKUPVALUE( Table2[Changed], Table2[ARD Key],Table1[ARD Key], Table2[Material Key],Table1[Material Key] )
I have attached my sample file based on your example data. Take a look at that and see if it helps.
Hello @Anonymous give these a try.
Corrected Quantity = LOOKUPVALUE ( Table2[Corrected Quantity], Table2[ARD Key], Table1[ARD Key], Table2[Material Key], Table1[Material Key] )
Changed = LOOKUPVALUE ( Table2[Changed], Table2[ARD Key], Table1[ARD Key], Table2[Material Key], Table1[Material Key] )
Possibly a better option though is to add a calculated column to both tables that combines [ARD Key] & [Material Key] then you could just join the two tables on that column.
You could also add a step to your import of table 1 in power query where you bring in the value from table 2, that way you wouldn't even need to load table 2 in the final model.
Thank you for the reply.
The formula unfortunately does not work for me.
It gives the error that 'a table of multiple values was supplied where a single value was expected'.
Any further suggestions regarding this or may be an alternate solution?
Hello @Anonymous
Can you share your .pbix file? You can uploade it to OnceDrive or DropBox and post the link here.
Unfortunately I cannot. It is work related confidential stuff 😞
Without seeing the data I can't tell you what the problem is. My formula works against your expample data.
Changed = LOOKUPVALUE( Table2[Changed], Table2[ARD Key],Table1[ARD Key], Table2[Material Key],Table1[Material Key] )
I have attached my sample file based on your example data. Take a look at that and see if it helps.
Got mine to work too.
Needed to tweak the formula a bit as there was another key which had a relationship with table 2.
Thanks for the help 🙂
PBi Community is great 🙂
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |