Search code examples
powerquerym

Using Power Query / Get & Transform (Excel 2016) for combining tables

How could I combine multiple tables based on the common index column "country" to produce a currency conversion as shown in the images below:

Local Currency Transactions * FX Rates = Currency Adjusted

enter image description here

enter image description here

enter image description here

Solution

  • I don't think the math from (A) and (B) lines up with (C). If I have 2 UK pounds, that should be close to 3 USD, not thirty cents.

    What you want to combine rows between two tables is generally called a left join. You can click the Merge button to combine the rows. You want to do a join from (A) and add (B) to it. Once you have that, you can use the Add Columns with divide and multiply to calculate the currency rate and apply it.

    The finished solution would look something like the final query:

    section Section1;

shared LocalCurrencyTransactions = let
    Source = Csv.Document("Country,LocalCurrencyAmounts
    US,1
    UK,2.13
    JAPAN,328.08
    INDIA,66.56
    US,2
    UK,0.71
    JAPAN,109.36
    INDIA,133.12"),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Country", type text}, {"LocalCurrencyAmounts", type number}})
in
    #"Changed Type";

shared #"FX Rates" = let
    Source = Csv.Document("Country,LocalCurrency,USD
    US,1,$1.00
    UK,0.71,$1.00
    JAPAN,109.36,$1.00
    INDIA,66.56,$1.00"),
    #"Promoted Headers" = Table.PromoteHeaders(Source),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Country", type text}, {"LocalCurrency", type number}, {"USD", Int64.Type}})
in
    #"Changed Type";

shared CurrencyAdjustedToUSD = let
    Source = LocalCurrencyTransactions,
    #"Merged Queries" = Table.NestedJoin(Source,{"Country"},#"FX Rates",{"Country"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"LocalCurrency", "USD"}, {"NewColumn.LocalCurrency", "NewColumn.USD"}),
    #"Inserted Division" = Table.AddColumn(#"Expanded NewColumn", "NewColumn.ConversionRate", each [NewColumn.USD] / [NewColumn.LocalCurrency], type number),
    #"Inserted Multiplication" = Table.AddColumn(#"Inserted Division", "USD", each List.Product({[LocalCurrencyAmounts], [NewColumn.ConversionRate]}), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Multiplication",{"NewColumn.LocalCurrency", "NewColumn.USD", "NewColumn.ConversionRate"})
in
    #"Removed Columns";