'Currency Conversion In PowerBI
I have a table containing sales data, but in different currencies. I would like to have my sales data to show sales in GBP only and also total sales in GBP
Input table of sales
Currency exchange rate table
Final Output table]
Hope to find a solution to this as the sales are per date so conversion has to be as per rate on that date.
Solution 1:[1]
Load the second table into powerquery (data .. from table/range ... [x] headers) , name the query Currency Conversion Rate in the upper right, and file ... close and load to ... only connection
Load the first table into powequery same methodology. Use any name
home... merge queries, choose Currency Conversion Rate table from drop down on bottom
Click currency on top and Currency name on bottom to match them. Hold down CTRL key and repeat for Invoice date and exchange rate date. Leave join kind as left outer. Hit okay to accept other options
Use arrows atop new column to expand [x] exchange rate field
Add column .. custom column ... and insert forumula to multiply the relevant fields such as
= [price per unit]*[Exchange rate]
Add column .. custom column ... and insert forumula to multiply the relevant fields
= [unit sold]*[price per unit]*[Exchange rate]
file close and load
I get that yours was a quick example, but note that powequery is case sensitive, and your two table examples use euro in one table and EURO in the other table so normally they will not match on the merge. It is also sensitive to spelling, so using "curreny name" as a column header and then coding for "currency name" would not work
sample full code that could be dumped into home ... advanced editor...
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Book ID", Int64.Type}, {"Invoice date", type datetime}, {"Currency", type text}, {"unit sold", Int64.Type}, {"price per unit", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Invoice date", "Currency"}, #"Currency Conversion Rate", {"Exchange rate date", "Currency Name"}, "Currency Conversion Rate", JoinKind.LeftOuter),
#"Expanded Currency Conversion Rate" = Table.ExpandTableColumn(#"Merged Queries", "Currency Conversion Rate", {"Exchange rate"}, {"Exchange rate"}),
#"Added Custom" = Table.AddColumn(#"Expanded Currency Conversion Rate", "Unit price in GBP", each [price per unit]*[Exchange rate]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Total in GBP", each [unit sold]*[price per unit]*[Exchange rate])
in #"Added Custom1"
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |