Forum Discussion
RichardJ
Responsive Resident
Responsive ResidentMerging table in Power Query using a timestamp between two timestamp values in calendar table
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
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"
2 Replies
CNENFRNLCommunity Champion
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"
