Replacing Nulls with part number

[Jack6627]

I have a dataset that looks something like the image below.

Each part gets ordered at random intervals over the course of the year. I have just shown a sample of the Date column but imagine the Date column to have 368 days for each part (31/1/2020 to 02/09/2021). This interval is fixed. When the part gets ordered, the corresponding value in the Part and Quantity fields is populated but when not, both are Null.  I would like to replace the Null values in the Part column with the appropriate Part number for that year.

Please note, that it is possible that a part gets ordered on say 20th of June 2021 and then does not get ordered at all, and the next part gets ordered on the 10th of October, 2020. In this case all the parts and Quantity fields from 21st of June to 9th of October will be Null and so, the only way to know when a parts' cycle is completed is by looking at the date values.

Appreciate your help! 🙂

[mbarone]

Is it the case that the null part numbers just need to be replaced with the most recent part number looking at the previous rows?  Your specific example above does not seem to rely on the date column at all.  It appears to just rely on simply knowing what the "last known" part number was.  If that's indeed the case, that can be solved with a Multrow Tool , but I can't tell.

[Jack6627]

Hi @mbarone . Thank you for the quick response. Sorry about the confusion, but no, that is not the case. You would need to rely on the date column to know when a new part's entry will start. For eg ABCD will occur 368 times (08/31/2020 to 09/02/2021) followed by EFGH 368 times, and so on, and so forth. In the screenshots, I have only shown 6 date values for ABCD out of the 368 instances. Please let me know if this is unclear and I will generate a dummy excel if needed. 🙂

[mbarone]

How does the data "come in" ?   On a daily basis?  Are dates/rows always being added?  Is there a point in time when a new part comes in 'for the first time' in that year?  Would need to know more about the process.

Perhaps setting up a workflow to run daily that scans the data file, takes the minimum date for each part number, then generates rows based on that date and if the date doesn't exist already it adds it?  For example, part ABCD has min date 8/31/2020, so next step is to generate rows for each day up to (8/31/2020 plus 367 days). Then for "new" data coming in, update the appropriate row.  Not sure if something like that would work - all depends on the process flow.

[Nanoq · Accepted solution]

Hey there
The fact that the first entry for each item doesnt have to have a value dont exactly make it easier, but i think i got it.
The idea is that the entries for each part number seem to be sequential, so we can group items by every time the date "resets" 
Once that is done, its just a matter of assigning a part number to each row in a group, and you should have the correct parts for each date.
It works for your example data, but there might be some complications im not taking into account.

check out the attatched workflow
Let me know if this is what you expected

[Jack6627]

Hi @Nanoq thank you for your response. Yes, this is exactly what I was looking for! Clever bit of trick adding the helper column 🙂