Forum Discussion
currency conversion
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.
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
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 !