Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
MdJ83
Helper II
Helper II

Combine two tables based on date ranges.

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 CurrencyTo CurrencyEffective DateMultiplier  GL DateBase CurrencyAmount
GBP AUD31/12/20230.564  2/01/2023GBP   45,826.00
GBP AUD31/03/20230.5443  4/04/2023GBP   23,468.00
GBP AUD31/05/20230.5237  2/03/2023CAD   12,456.00
GBP AUD30/06/20230.5318  30/06/2023CAD     3,256.00
CADAUD31/12/20230.8825     
CADAUD31/03/20230.8887     
CADAUD31/04/20230.9067     
CADAUD30/05/20230.9173     
         
         
After        
GL DateBase CurrencyAmountMultiplier     
2/01/2023GBP       45,826.000.564     
4/04/2023GBP          23,468.000.5443     
2/03/2023CAD          12,456.000.8825     
30/06/2023CAD            3,256.000.9173     
1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

@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

 

 

View solution in original post

4 REPLIES 4
MdJ83
Helper II
Helper II

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
Super User
Super User

@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

 

 

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
Regular Visitor

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"

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors