Hi,
I have a data table (1. Data table). I want to add a column in Power Query called "Exchange Rate", and the rate values come from the 2nd table - Currency Exchange Rate.
Can anyone help me with the M function to do it please? Thank you.
1. Data table
| Reporting Date | Amount Spent | Currency |
| 9/1/2022 | 52.1 | CAN |
| 9/3/2022 | 10.06 | CAN |
| 9/14/2022 | 5.74 | CAN |
| 9/11/2022 | 68.9 | CAN |
| 9/12/2022 | 77.3 | CAN |
Data table M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ31DcyMDJS0lEyNdIzBFLOjn5KsTogGWOYjKGBnoEZipShCVyXnrkJqhTcQDMLPUtUKSOYlLm5njFMKhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reporting Date" = _t, #"Amount Spent" = _t, Currency = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Reporting Date", type date}, {"Amount Spent", type number}, {"Currency", type text}})
in
#"Changed Type"
2. Currency Exchange Rate
| CurrencyExchange_FromCurrency | CurrencyExchange_ToCurrency | CurrencyExchange_FromDate | CurrencyExchange_ToDate | CurrencyExchange_Rate |
| CA$ | US$ | 9/12/2022 | 9/13/2022 | 0.7704 |
| CA$ | US$ | 9/9/2022 | 9/11/2022 | 0.7672 |
| CA$ | US$ | 9/8/2022 | 9/8/2022 | 0.7624 |
| CA$ | US$ | 9/7/2022 | 9/7/2022 | 0.7596 |
| CA$ | US$ | 9/6/2022 | 9/6/2022 | 0.7611 |
| CA$ | US$ | 9/2/2022 | 9/5/2022 | 0.7621 |
| CA$ | US$ | 9/1/2022 | 9/1/2022 | 0.7595 |
Currency Exchange Rate M Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc5BCoAwDATAvwSPxTbRJs1RfIJ4Kv3/N0RBkmovyx6GZWuFfZsgwHncqVEjJaKnIr09zcJC0MJXF9PqMa0DLIaLw1l5gNmw+GXEASbD3N0YYTRM3Y38wyUuTmM3LdDaBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CurrencyExchange_FromCurrency = _t, CurrencyExchange_ToCurrency = _t, CurrencyExchange_FromDate = _t, CurrencyExchange_ToDate = _t, CurrencyExchange_Rate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CurrencyExchange_FromCurrency", type text}, {"CurrencyExchange_ToCurrency", type text}, {"CurrencyExchange_FromDate", type date}, {"CurrencyExchange_ToDate", type date}, {"CurrencyExchange_Rate", type number}})
in
#"Changed Type"
