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 quer,y 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!
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.
You can follow an approach similar to this in Power Query.
- Sort by
TransDate - GroupBy
Item - Then in the nested Grouped table:
- Remove the first row
- Add new column for
NextDateifInventoryCount> 0 - Fill-up the
NextDate - Calculate days by subtracting
TransDatefromNextDate - Filter on
Daysis greater than 0 - 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]}}),
#"RemoveFirstRow" = Table.TransformColumns(#"Grouped Rows", { "Rows", each Table.Skip(_,1) }),
#"Add NextDate" = Table.TransformColumns(#"RemoveFirstRow", { "Rows", each Table.AddColumn(_, "NextDate", each if [InventoryCount] > 0 then [TransDate] else null) }),
#"FillUp NextDate" = Table.TransformColumns(#"Add NextDate", { "Rows", each Table.FillUp(_ ,{"NextDate"}) }),
#"Add days" = Table.TransformColumns(#"FillUp NextDate", { "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:
can we use DAX to get this output?
Column =
VAR _last =
MAXX (
FILTER (
'Table',
'Table'[Item] = EARLIER ( 'Table'[Item] )
&& 'Table'[TransDate] < EARLIER ( 'Table'[TransDate] )
),
'Table'[TransDate]
)
VAR _lastinventory =
MAXX (
FILTER (
'Table',
'Table'[Item] = EARLIER ( 'Table'[Item] )
&& 'Table'[TransDate] = _last
),
'Table'[InventoryCount]
)
VAR _lastex =
MAXX (
FILTER (
'Table',
'Table'[Item] = EARLIER ( 'Table'[Item] )
&& 'Table'[TransDate] = _last
),
'Table'[explanation]
)
RETURN
IF (
_lastinventory = 0
&& CONTAINSSTRING ( _lastex, "depleted" ),
DATEDIFF ( _last, 'Table'[TransDate], DAY )
)
1