Power Query: Lookup a value in a give timeframe and return in custom column

[seppel123]

Hi everyone,

i have a problem with a dataset that i tried to solve with a custom column in Power Query.

My data shows how long a Container ("Container", Nr. of container) is in the Depot ("Depot", time in days) and how long that Container was filled up again ("Filling 1", "Filling 2" or "Filling 3" time in hours). The problem is, that for every step there is a new line of data and every container can come multiple times in the year. 

Example:

Container	Filling 1	Filling 2	Filling 3	Depot	Start	End
GESU808378-9	10	null	null	null	30.08.2023	30.08.2023
GESU808378-9	null	null	null	8,816666667	30.08.2023	08.09.2023
GESU808378-9	10	null	null	null	30.08.2023	30.08.2023
GESU808378-9	null	10,21666667	null	null	05.09.2023	06.09.2023
GESU808378-9	null	null	null	null	25.09.2023	null
GESU808378-9	23	null	null	null	25.09.2023	25.09.2023
GESU808378-9	25	null	null	null	18.10.2023	18.10.2023
GESU808378-9	null	null	null	7,684722222	18.10.2023	26.10.2023
GESU808378-9	null	1,866666667	null	null	25.10.2023	25.10.2023
GESU808378-9	21	null	null	null	06.11.2023	06.11.2023
GESU808378-9	null	null	null	3,938194444	06.11.2023	10.11.2023
GESU808378-9	null	1,783333333	null	null	08.11.2023	08.11.2023
GESU808378-9	null	null	null	4,970138889	29.11.2023	04.12.2023
GESU808378-9	null	null	null	null	29.11.2023	null
GESU808378-9	null	3,266666667	null	null	30.11.2023	30.11.2023

What i would like is to lookup the time frame inbetween that Container was in the Depot (start and end dates of that line where "Depot" has a value) and check if in that time frame there are values for that specific container in Filling 1,2 or / and 3. If thats the case fill the new custom column with the end date of the line of Filling 1, 2 or 3. At the end it could look like this: 

Thank you in advance and best regards

Sebastian

[lbendlin]

he problem is, that for every step there is a new line of data and every container can come multiple times in the year.

The first issue can be handled by unpivoting, the second one is covered by the Start Date.

fill the new custom column with the end date of the line of Filling 1, 2 or 3

which one? a random one? the last? the first?

[seppel123]

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

[lbendlin · Accepted solution]

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.