currency conversion

[hperashmi83]

Need Urgent help for currency conversion. I have a table with bank balance in local currency and I need to convert the amount to USD. I also have exchange rate table which has exchange rate for multiple days... The conversion must happen based on latest date on the list.

Table1 

Statement Date	Currency	Amount in Local Currency
1/26/2022	BRL	800.00
1/12/2022	EUR	200.00
1/26/2022	EUR	1,800.00
1/26/2022	SEK	2,000.00
1/5/2022	JPY	4,250

 Table 2

From currency

To-currency

Valid from

Exchan

			ge Rate
BRL	USD	02.02.2022	5.3016
BRL	USD	01.02.2022	5.2798
BRL	USD	31.01.2022	5.30865
BRL	USD	28.01.2022	5.4065
EUR	USD	02.02.2022	0.88527
EUR	USD	01.02.2022	0.88901
EUR	USD	31.01.2022	0.89194
EUR	USD	28.01.2022	0.89562
SEK	USD	02.02.2022	9.20325
SEK	USD	01.02.2022	9.2777
SEK	USD	31.01.2022	9.3313
SEK	USD	28.01.2022	9.4287
JPY	USD	02.02.2022	114.39
JPY	USD	01.02.2022	114.785
JPY	USD	31.01.2022	115.23
JPY	USD	28.01.2022	115.185

expected result = 

Statement Date	Currency	Amount in Local Currency	Amount in USD
1/26/2022	BRL	800.00	800.00 *5.3016 =4241.28

[sajjadkhan25]

Can you please share pbix if it's completed please

[v-yingjl · Accepted solution]

Hi @hperashmi83 ,

You can modify your table1 query like this in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3MtM3MjAyUtJRcgryAZIWBgZ6BgZKsTogSUMjmKRraBCQNEKWROiESBrqWGCXDnb1BunVMUCWNoXJegVEAkkTHSNToFQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Statement Date" = _t, Currency = _t, #"Amount in Local Currency" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Statement Date", type date}, {"Currency", type text}, {"Amount in Local Currency", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Currency"}, Table2, {"From currency"}, "Table2", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Amount in USD", each Table.Max([Table2],"Valid from")[Exchange Rate] * [Amount in Local Currency],type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Table2"})
in
    #"Removed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[serpiva64]

Hi, 

i think there is an error in your exemple.

the change of 5.3016 for BRL is valid from 02/02/2022 but the bank balance is in date 1/26/2022 which is before.

please control your data to be sure to obtain the right result 

[hperashmi83]

what ever date we receive the statement we want to convert that value with current Excahnge rate so we just have to pick the currency and amount from Table 1 and currency and excahnge rate from Table 2. So please help provide me a solution

[serpiva64]

Hi, this is the result:

To obtain it i create the following 2 query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3MtM3MjAyUtJRcgryAZIWBgZ6BgZKsTogSUMjmKRraBCQNEKWROiESBrqWGCXDnb1BunVMUCWNoXJegVEAkkTHSNToFQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Statement Date" = _t, Currency = _t, #"Amount in Local Currency" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Statement Date", type text}, {"Currency", type text}, {"Amount in Local Currency", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",",","",Replacer.ReplaceText,{"Amount in Local Currency"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".",",",Replacer.ReplaceText,{"Amount in Local Currency"}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value1", {{"Statement Date", type date}}, "en-US"),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Amount in Local Currency", type number}}),
#"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Statement Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Currency"}, {{"Count", each _, type table [Statement Date=nullable date, Currency=nullable text, Amount in Local Currency=nullable number]}, {"Current", each List.Max([Statement Date]), type nullable date}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Statement Date", "Amount in Local Currency"}, {"Statement Date", "Amount in Local Currency"}),
#"Merged Queries" = Table.NestedJoin(#"Expanded Count", {"Currency"}, #"Table (2)", {"From currency"}, "Table (2)", JoinKind.LeftOuter),
#"Expanded Table (2)" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Exchange rate"}, {"Exchange rate"}),
#"Added Custom" = Table.AddColumn(#"Expanded Table (2)", "Amount in USD", each [Amount in Local Currency]*[Exchange rate]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Amount in USD", type number}})
in
#"Changed Type2"

and the second:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdExCsMwDIXhu3gOxk+yY2ktzdJ2KAkZSsj9r1Gn7VBZAWMs+BA/eNvCZX6EIazLtd2JYjuUiNpQIieMYR86A2OoqjjDzeB/j4zFIRKDcvqZaZ3Pg1IUKVQ9Qoc0wSGT1JBCs0Mm6UBlpA9apvt5k7YHU/EIFtVanTFJGpnBzpgijZnku+f2fJ0HATmyeoPOVCkOmSCgfS47Y4IOg2PR/gY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"From currency" = _t, #"To-currency" = _t, #"Valid from" = _t, #"Exchange rate" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Valid from", type date}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",".",",",Replacer.ReplaceText,{"Exchange rate"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"Exchange rate", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type1", {"From currency"}, {{"Count", each _, type table [From currency=nullable text, #"To-currency"=nullable text, Valid from=nullable date, Exchange rate=nullable number]}, {"Current", each List.Max([Valid from]), type nullable date}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"To-currency", "Valid from", "Exchange rate"}, {"To-currency", "Valid from", "Exchange rate"}),
#"Added Conditional Column" = Table.AddColumn(#"Expanded Count", "Custom", each if [Valid from] = [Current] then [Exchange rate] else null),
#"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([Custom] <> null))
in
#"Filtered Rows"

Some steps are not necessary for you because i needed to transform date and number to local.

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !