I have a dataset similar to the one below and I am trying to do a gap and island analysis on it.
| Item | TransDate | InventoryCount | explanation |
|---|---|---|---|
| Bicycle | 01/01/2023 | 0 | First time the item Bicycle has been insert in database, this record should be disregarded for the calculation |
| Bicycle | 01/02/2023 | 15 | Bicycle first stock arrival |
| Bicycle | 01/05/2023 | 10 | Bicycle stock partially depleted |
| Bicycle | 01/07/2023 | 0 | Bicycle stock completely depleted |
| Bicycle | 01/31/2023 | 15 | Bicycle second stock arrival |
| Car | 01/01/2023 | 0 | First time the item Car has been insert in database, this record should be disregarded for the calculation |
| Car | 01/20/2023 | 3 | Car first stock arrival |
| Car | 01/21/2023 | 0 | Car first stock depleted |
| Car | 01/25/2023 | 5 | Car second stock arrival |
| Car | 01/26/2023 | 0 | Car second stock depleted |
| Car | 02/10/2023 | 2 | Car third stock arrival |
| Motorcycle | 01/01/2023 | 0 | First time the item Motorcycle has been insert in database, this record should be disregarded for the calculation |
| Motorcycle | 01/02/2023 | 10 | Motorcycle first stock arrival |
| Motorcycle | 01/05/2023 | 5 | Motorcycle stock partially depleted |
| Motorcycle | 01/08/2023 | 0 | Motorcycle first stock completely depleted |
| Motorcycle | 01/28/2023 | 15 | Motorcycle second stock arrival |
What I am trying to do, in power query, is to find the number of out of stock times and the length (in days) of the out of stock situation something like:
Bicycle 1 time out of stock for 25 days
Car 2 times out of stock for 4 and 15 days
Motorcycle 1 time out of stock for 20 days.
There is not much online about gap and island analysis in power query and the only thing I found doe snot really help. I’ve been able to figure out something via SQL but I have no access to the original source.
Any help would be greatly appreciated!
New contributor
Test Test is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.