Combine two tables based on date ranges.

[MdJ83]

I'm not sure if anyone is able to assist to combine two tables based on different date ranges or having a lookup in dax to add values to an existing table based on date ranges. This has to do with foreign currency translation. Where different rates are applicable for different date ranges.

Example

Two Tables 

From Currency	To Currency	Effective Date	Multiplier			GL Date	Base Currency	Amount
GBP	AUD	31/12/2023	0.564			2/01/2023	GBP	45,826.00
GBP	AUD	31/03/2023	0.5443			4/04/2023	GBP	23,468.00
GBP	AUD	31/05/2023	0.5237			2/03/2023	CAD	12,456.00
GBP	AUD	30/06/2023	0.5318			30/06/2023	CAD	3,256.00
CAD	AUD	31/12/2023	0.8825
CAD	AUD	31/03/2023	0.8887
CAD	AUD	31/04/2023	0.9067
CAD	AUD	30/05/2023	0.9173
After
GL Date	Base Currency	Amount	Multiplier
2/01/2023	GBP	45,826.00	0.564
4/04/2023	GBP	23,468.00	0.5443
2/03/2023	CAD	12,456.00	0.8825
30/06/2023	CAD	3,256.00	0.9173

[MdJ83]

This works well but does cause having a duplicate table with the same transactional information and cannot delete the orginal table.

Is there a way to combine the s_rates into the table with the amounts so that before read the table with the amounts 

[AlienSx · Accepted solution]

@MdJ83 make sure you use correct dates (you have 31st of April in your data). Also make sure you have dates in rates table earlier than min date in GL table. Replace your_rates_table and your_GL_table in code below with correct references to your tables.

let
    // this is your table with rates. Had to trim ccy column later.
    s_rates = your_rates_table,
    date_type = Table.TransformColumnTypes(s_rates, {{"Effective Date", type date}}, "en-AU"),
    trimmed = Table.TransformColumns(date_type,{{"From Currency", Text.Trim, type text}}),
    // this is your table with amounts
    before = your_GL_table,
    b_type = Table.TransformColumnTypes(before, {{"GL Date", type date}}, "en-AU"),
    // make a record with ccy name fields and list of dates and rates sorted by date
    rates = 
        Table.Group(
            trimmed, "From Currency", 
            {{"rates", each List.Sort( List.Zip({_[Effective Date], _[Multiplier]}), {(x) => x{0}, Order.Descending})}}
        ),
    rec = Record.FromList(rates[rates], rates[From Currency]),
    // add Multiplier column.
    after = 
        Table.AddColumn(
            b_type, "Multiplier",
            each [a = Record.FieldOrDefault(rec, [Base Currency], null),
            b = a{List.PositionOf(a, [GL Date], Occurrence.First, (x, y) => x{0} <= y)},
            c = if b = -1 then null else b{1}][c]
        )
in
    after

[MdJ83]

Is there a way to not make a new table but to include the mutliplier into table with the amounts. I presume one would need to change the before to the current table but not sure if that is creating a cross reference.

[DavidBI]

This code works, but does include quite a few steps and merging queries. I am sure someone in here will post a more elegant solution. My solution requires you to load both tables into Power Query and go from there.

First table with the multiplier data in which you can change my source step to your source step:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From Currency", type text}, {"To Currency", type text}, {"Effective Date", type date}, {"Multiplier", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Added Index1",{{"Index", Order.Ascending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"From Currency", "To Currency", "Index.1"}, #"Sorted Rows", {"From Currency", "To Currency", "Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Effective Date"}, {"Effective Date.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "EndDate", each Number.From([Effective Date.1])-1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type1",{{"From Currency", Text.Trim, type text}})
in
    #"Trimmed Text"

second table in which you also have to change the source accordlingly:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GL Date", type date}, {"Base Currency", type text}, {"Amount", type number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Base Currency"}, DateRanges, {"From Currency"}, "DateRanges", JoinKind.LeftOuter),
    #"Expanded DateRanges" = Table.ExpandTableColumn(#"Merged Queries", "DateRanges", {"Effective Date", "Multiplier", "Effective Date.1"}, {"Effective Date", "Multiplier", "Effective Date.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded DateRanges", "MultiplierV2", each if [Effective Date.1] = null and [GL Date] >= [Effective Date] then "keep" else if [GL Date] >= [Effective Date] and [GL Date] <=[Effective Date.1] then "keep" else
"delete"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([MultiplierV2] = "keep")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"GL Date", "Base Currency", "Amount", "Multiplier"})
in
    #"Removed Other Columns"