Forum Discussion
Power Query: Lookup a value in a give timeframe and return in custom column
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
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.
3 Replies
- lbendlin
Super User
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 3which one? a random one? the last? the first?
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
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.
