copying one column values to another based on two maching key values

[Anonymous]

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. 

table 1 to which i need to add the columns

table 2 from which I need to copy columns named 'Corrected Quantity' and 'Changed' to table 1 '

and finally my table 1 should look like the pic below

resultant table 1 that I need to obtain

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. 🙂

[jdbuchanan71]

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.

[Anonymous]

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?

[jdbuchanan71]

Hello @Anonymous 

Can you share your .pbix file?  You can uploade it to OnceDrive or DropBox and post the link here.

[Anonymous]

Unfortunately I cannot. It is work related confidential stuff 😞

[jdbuchanan71 · Accepted 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.

[Anonymous]

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 🙂