Forum Discussion
Index Match - Match row datas with table header
Currency Table unpivot no relation now
Thanks for help. As suggested by you, I have unpivot the table as below: But unable to write the lookup formulae- As I need to match date as well as currency before picking up the value. Could you help me with the formulae.
Thanks. Great. It helps. Is there any other way that it reads the Currency abreviations like OMR, USD etc and match it. As my table has several currencies and more than 3650 dates.
Super UserAnonymous in thst case you have to unpivot your currency table, to do so, go to query editor, select date column in currency table, right click, and select unpivot other columns. You will get two columns attribute and value, rename those to currency and rate.
Once these changes are applied, we don't need to set relationship between currency and sales table but use lookupvalue function, read here about this function.
Currency Table unpivot no relation now
Thanks for help. As suggested by you, I have unpivot the table as below: But unable to write the lookup formulae- As I need to match date as well as currency before picking up the value. Could you help me with the formulae.
- parry2k
Anonymous here it is, add a column in data table
FX Rate = LOOKUPVALUE
( CurrencyTable[Value], CurrencyTable[Date], DataTable[Date], CurrencyTable[Currency], DataTable[Currency] ) Out of the two method, which method is best and consumes less RAM / more efficient. Thanks
Thanks a lot.