Get value from table by another rows value and the column name

[jonoren]

So, my problem is as follows:

Firstly, I have the following table for currency exchange rates (for all currencies):

and second, I have a table of reservations, each reservation has a currency code and a date. I want to add the specific currency exchange rate for each reservation, but cant seem to understand how to do so.

Thank you!

[jonoren]

Perfect thank you!

[KT_Bsmart2gethe]

Hi @jonoren ,

If I understood correctly, you want to get the value from the reservations table to the currency rates table.

There are a few ways to achieve it; however, without knowing what your reservations table's format is, I can only provide a brief direction:

1. Unpivot the currency rates table to have all the code unpivoted, so you have a date and the code columns as key identifiers to apply the merge function.

2. Add custom column and apply the Table.SelectRows(ReservationsTbl, (lookup)=> lookup[Date]=[Date Column from the reservation table] and lookup[Code]=[Code column from the reservation table])[return column name]{row number}

Regards

KT

[jonoren]

I actually want the other way around, the reservations table has hundreds of columns so I prefer not to share a screenshot, but you can think of it as a table that looks like this:

and I want it to be this (using the exchange rates table)

Thanks a lot!

[KT_Bsmart2gethe · Accepted solution]

Hi @jonoren ,

Step 1:

Unpivot columns

 Step2:

Merge

Outcome:

I hope it helps.

Regards

KT