Forum Discussion
Power Query: Lookup a value in a give timeframe and return in custom column
I'll give you the general direction. But I can't really judge how useful that would be to do in Power Query. I'd rather do it in DAX or not at all.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZJNDoIwEEbvwnps5gfa6QGMBzCuCDcg7ry/FQUHaBGNs6B8kL686bRtq9PxfFFUCXqIFVSE6XG99f1qEXSojpFlHjpYQbL7FZT8UGFJS28Yi7R/KhEC0yQxp2EzWaTgt5SyNq+FLWb4lgG8f25uNyEHaUoQUkc4QkzY20oAr3XgRy1p7HfQCNT7wjGnngyt2aIxlQTTeIjMrMawt0GBKEqxTrWkJZ/PNIKg8qy1m1o3/dqthhiQRFWHM4iWVjvi326lxZRu5XQ4XJyeoOGY0HV3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Container = _t, #"Filling 1" = _t, #"Filling 2" = _t, #"Filling 3" = _t, Depot = _t, Start = _t, End = _t]), #"Added Custom" = Table.AddColumn(Source, "Filling 1 end", each if [Depot] = "null" then null else try Table.SelectRows(Source,(k)=> [Container]=k[Container] and [Start]=k[Start] and k[Filling 1]<> "null")[End]{0} otherwise null) in #"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Thank you so much for your help.
Your Table by Start Date shows the results i would like to have in the table in Power Query. However, the listed end dates do not correspond with the end dates of the original data.
The end date of the line of Filling 1, 2 or 3 should not be a random one. It should be the one that lies in between the Startdate and the Enddate of the corresponding "Depot" line.
For example
Depot: Startdate 18.10.2023 and Enddate 26.10.2023.
Only Filling 2 has a value, so the Enddate 25.10.2023 is relevant and that value should be written in the new column. No other Filling dates fit with the timeframe between 18.10.2023 and 26.10.2023 for that specific container.
With this information further analysis of performance of the Depot is possible (how long each Container waits until it is filled, how long each Container waits when it is already filled to be picked up, ...).
Hope that helped to clarify the purpose.
Best regards
Sebastian
Super UserI'll give you the general direction. But I can't really judge how useful that would be to do in Power Query. I'd rather do it in DAX or not at all.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZJNDoIwEEbvwnps5gfa6QGMBzCuCDcg7ry/FQUHaBGNs6B8kL686bRtq9PxfFFUCXqIFVSE6XG99f1qEXSojpFlHjpYQbL7FZT8UGFJS28Yi7R/KhEC0yQxp2EzWaTgt5SyNq+FLWb4lgG8f25uNyEHaUoQUkc4QkzY20oAr3XgRy1p7HfQCNT7wjGnngyt2aIxlQTTeIjMrMawt0GBKEqxTrWkJZ/PNIKg8qy1m1o3/dqthhiQRFWHM4iWVjvi326lxZRu5XQ4XJyeoOGY0HV3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Container = _t, #"Filling 1" = _t, #"Filling 2" = _t, #"Filling 3" = _t, Depot = _t, Start = _t, End = _t]),
#"Added Custom" = Table.AddColumn(Source, "Filling 1 end", each if [Depot] = "null" then null else
try Table.SelectRows(Source,(k)=> [Container]=k[Container] and [Start]=k[Start] and k[Filling 1]<> "null")[End]{0} otherwise null)
in
#"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.