Hi,
I'm new to Power Query and I can't find a solution to the following problem. To put it in context I have a dataset showing daily items on stock. The data is generated almost every working day but sometimes there are random time gaps when no data is generated at all. Original dataset attached, the simplified data looks like this:
| Product ID | Date |
| A | 6.1.2020 |
| B | 6.1.2020 |
| C | 6.1.2020 |
| A | 9.1.2020 |
| B | 9.1.2020 |
| C | 9.1.2020 |
| A | 11.1.2020 |
| B | 11.1.2020 |
| A | 14.1.2020 |
| B | 14.1.2020 |
| C | 14.1.2020 |
| A | 15.1.2020 |
| B | 15.1.2020 |
| C | 15.1.2020 |
I managed to create a "running count" column using grouping and add index based on this tutorial ( https://www.youtube.com/watch?v=camNMc1zaBc&t=189s ) and I got the output shown in Table 1. As you can see, Product "C" was not present on 11/1/2020 but was present again on 14/1/2020 so the running count goes continuously on. The ouptut I need though, is the one shown in Table 2. I need the counting to start over anytime when product ID was not present the previous date and at least one product was.
Do you guys have any ideas how to solve this? I will be sooo glad for any advice.
Thank you so much!
Honzik
Hi Honzik,
That's a hard nut to crack, checking if the product is in previous period to reset the counter is mind blowing, have no idea why that reset is needed.
It is possible though, but not easy and probably slow.
See the file attached.
