excelpowerquerygaps-and-islands

How do I a gap and island analysis with a power query?

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 does not 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!

Solution

  • You can follow an approach similar to this in Power Query.

    1. Sort by TransDate
    2. GroupBy Item
    3. Then in the nested Grouped table:
      1. Remove top rows that are InventoryCount = 0
      2. Add new column for NextDate if InventoryCount > 0
      3. Fill-up the NextDate
      4. Remove duplicates on InventoryCount & NextDate - this remove consecutive 0
      5. Calculate days by subtracting TransDate from NextDate
      6. Filter on Days is greater than 0
      7. Expand table with the needed columns
    let
    Source = YourTable,
    #"Sorted Rows" = Table.Sort(Source,{{"TransDate", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Item"}, {{"Rows", each _, type table [Item=nullable text, TransDate=nullable date, InventoryCount=nullable number]}}),
    #"RemoveTop0Rows" = Table.TransformColumns(#"Grouped Rows", { "Rows", each Table.Skip(_, each [InventoryCount] = 0) }),
    #"Add NextDate" = Table.TransformColumns(#"RemoveTop0Rows", { "Rows", each Table.AddColumn(_, "NextDate", each if [InventoryCount] > 0 then [TransDate] else null) }),
    #"FillUp NextDate" = Table.TransformColumns(#"Add NextDate", { "Rows", each Table.FillUp(_ ,{"NextDate"}) }),
    #"Remove Consecutive zeros" = Table.TransformColumns(#"FillUp NextDate", { "Rows", each Table.Distinct(_, {"NextDate", "InventoryCount"}) }),
    #"Add days" = Table.TransformColumns(#"Remove Consecutive zeros", { "Rows", each Table.AddColumn(_, "Days", each Duration.Days([NextDate] - [TransDate]), Int64.Type) }),
    #"Filter Days" = Table.TransformColumns(#"Add days", { "Rows", each Table.SelectRows(_, each [Days] > 0) }),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Filter Days", "Rows", {"Days"}, {"Days"})
in
    #"Expanded Rows"

    To give you a resulting table similar to:
    enter image description here