Merging table in Power Query using a timestamp between two timestamp values in calendar table

[RichardJ]

Hi,

Please could someone advise if/how it is possible in Power Query to merge a column from a table where a timestamp is within a date/time range on another table.

In the pbix i've created a Production Calendar table (6am Friday til 5:59:59am the following Friday) and have a Measurements table where i'd like to find the Production Week for the Measurement from the Calendar table.

PBIX can be found here

https://www.dropbox.com/s/itxcl36x34zuk93/Production%20Week%20Calendar.pbix?dl=0

Question as below

Merge Question

Thanks,

Richard

[CNENFRNL · Accepted solution]

Easy enough

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNA3MjAyUDC1MrW0MjBQcPRV0lEyNAASAYnFxUAKJBtvYKQUq4Os3Ayo1srAEKrcEF25MZpyC7BymOlGQMItMTMHWXksAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TimeStamp = _t, Measurement = _t, Quality = _t, #"Desired Result" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TimeStamp", type datetime}, {"Measurement", Int64.Type}}),

    #"Added YrWk" = Table.AddColumn(#"Changed Type", "YrWk", each let dt = Date.From([TimeStamp] - #duration(0,6,0,-1)) in Number.ToText(Date.Year(dt)*100 + Date.WeekOfYear(dt, Day.Friday), "0000_00"))
in
    #"Added YrWk"

[RichardJ]

@CNENFRNL Now that is a beautiful solution - No Merge required.
Nice work and thanks for the lesson.