Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
2 years ago

Bring value from another table that is between two dates

I am working on a project where I have two tables   Table1 is transaction data and contains a specific date of transaction.  i.e.   Customer Item Date Walmart Funyuns 1/13/2023    ...
smozgur's avatar
smozgur
Helper I
2 years ago

One way of doing this. Remember to change Table1 and Table2 with your own source tables.

 

 

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1ScFTSUQooyk8pTS4Bs431TfWNDIyMlGJ18KoyNgQpM0ZV5oSmzEzf0ASLaejKDA31TaCmxQIA", BinaryEncoding.Base64), Compression.Deflate)), {"Customer", "Item", "Date"}),
    ChangeTypes1 = Table.TransformColumnTypes(Table1,{{"Customer", type text}, {"Item", type text}, {"Date", type date}}),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1ScFTSUQooyk8pTS4Bsw31DfWNDIyMQUwjfWM4x1QpVgdJmxN2bUbI2kAcM1RtOGzD0GZOlG0YjrRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), {"Customer", "Item", "valid from", "valid to", "price"}),
    ChangeTypes2 = Table.TransformColumnTypes(Table2,{{"Customer", type text}, {"Item", type text}, {"valid from", type date}, {"valid to", type date}, {"price", type number}}),


    Source = Table.NestedJoin(ChangeTypes1, {"Customer", "Item"}, ChangeTypes2, {"Customer", "Item"}, "Price", JoinKind.LeftOuter),
    ExpandPrices = Table.ExpandTableColumn(Source, "Price", {"valid from", "valid to", "price"}),
    FilterMatchedPrices = Table.SelectRows(ExpandPrices, each [valid from] <= [Date] and [valid to] >= [Date]),
    RemoveColumns = Table.RemoveColumns(FilterMatchedPrices, {"valid from", "valid to"})
in
    RemoveColumns