Bring value from another table that is between two dates

Hi Syndicate_Admin ,

How about this:

The idea was to unfold the dates in table2 into one row per date between valid from and valid to and afterwards merge table1 with the unfolded table2 on the new date column (make sure the two columns ahve the same data type). Note, this could lead to a very high number of rows in table2 as you would be doing this unfolding for each row in table2 (before the transformation). That is creating a new row for all days between the two dates per customer and item. I'd probably recommend to push this transformation upstream or maybe considering some DAX to calculate the price on the fly. 

But as per your query, here a solution in Power Query M:

1. create a new custom column in table2 with this M code: Number.From ( [valid from] ) ..Number.From ( [valid to] 

2. unfold/expand the new column

3. change the data type of the new column to date 

(the first three steps I took from here:

https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#unfold-expand-create-rows-between-two-dates)

4. go back to table1 and merge table1 with table2 on the date columns

5. expand the price from the Table2 Table

Here the M code for table2:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk/MyU0sKlHSUXIrzasszSsGsgz1DfWNDIyMgUxjINsIxlEx1TMwUIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Item = _t, #"valid from" = _t, #"valid to" = _t, price = _t]),
    #"Changed Type 1" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Item", type text}, {"valid from", type date}, {"valid to", type date}, {"price", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type 1", "Date", each { Number.From ( [valid from] ) ..Number.From ( [valid to] ) }),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type 2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}})
in
    #"Changed Type 2"

 Here the M code for table1:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk/MyU0sKlHSUXIrzasszSsGsgyN9Q31jQyMjJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Item = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Item", type text}, {"Date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Customer", "Item", "Date"}, Table2, {"Customer", "Item", "Date"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"price"}, {"price"})
in
#"Expanded Table2"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

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

thank you, both.