Forum Discussion

hperashmi83's avatar
hperashmi83
Frequent Visitor
4 years ago
Solved

currency conversion

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 multi...
Need Help
  • v-yingjl's avatar
    v-yingjl
    4 years ago

    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.

v-yingjl's avatar
v-yingjl
Community Support
4 years ago

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.