Hello,
I have a power query report which is pulling sales data by month from our ERP system in Canadian Dollars. I would like to pull a parameter table from excel which has the CAD to USD fx conversion rates per month(months are the column headers). I need to pull this Excel table into power query and reference the data in a formula on the sales table in order to convert the sales into USD based on whatever FX rate is on the excel table for the month. I have had success in the past creating two column parameter tables in the past which looks up the first column and pulls the data entered in the 2nd column(look up fnGetParameter) but I don't know how to make the header column the reference instead (basically a flipped version).
Thanks.
I have a power query report which is pulling sales data by month from our ERP system in Canadian Dollars. I would like to pull a parameter table from excel which has the CAD to USD fx conversion rates per month(months are the column headers). I need to pull this Excel table into power query and reference the data in a formula on the sales table in order to convert the sales into USD based on whatever FX rate is on the excel table for the month. I have had success in the past creating two column parameter tables in the past which looks up the first column and pulls the data entered in the 2nd column(look up fnGetParameter) but I don't know how to make the header column the reference instead (basically a flipped version).
Thanks.
