Running count starting over when the counted value is not present previous date

Hi Anonymous ,

you have to modify the code like so:

let
fnIdx = 
(myTable as table) =>
let
partition = Table.Buffer(myTable),
dates = AllDatesWthIndex,
    #"Merged Queries" = Table.NestedJoin(partition, {"Date"}, dates, {"Date"}, "Changed Type", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries", "Changed Type", {"Index"}, {"Index"}),
    Custom1 = Table.ToColumns(#"Expanded Changed Type"),
    IndexedTable = Table.Buffer( Table.Sort(Table.FromColumns(Custom1 & { {null} & List.RemoveLastN(List.Last(Custom1),1) }, {"ID", "Date", "Warehouse", "Index", "PrevIndex"}), "Index") ),
    Initial = IndexedTable{0} & [Counter = 0, Idx = 1],
    Custom3 = List.Generate( ()=> Initial,
        each [Counter] < Table.RowCount(IndexedTable),
        each [ Counter = [Counter] + 1,
               Idx = if IndexedTable[Index]{Counter} - IndexedTable[PrevIndex]{Counter} = 1 then [Idx] + 1 else 1
               ]),
    #"Converted to Table" = Table.FromList(Custom3, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Idx"}, {"Idx"})[Idx],
    Custom2 = Table.FromColumns( Table.ToColumns( IndexedTable ) & { #"Expanded Column1" })
in
    Custom2,

    Origine = dataSample,
    Selection = Table.FirstN(Origine,CountRecordsImke),
    AllDates=Table.Buffer( Table.Distinct(Table.SelectColumns(Selection, {"Date"})) ),
    AllDatesWthIndex = Table.AddIndexColumn(AllDates, "Index", 0, 1, Int64.Type),
    AllProducts = Origine,
    #"Grouped Rows" = Table.Group(Selection, {"ProductID"}, {{"ProductPartition", each _}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fnIdx([ProductPartition])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column2", "Column3", "Column6"}, {"Date", "Warehouse", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"ProductPartition"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}, {"Index", Int64.Type}, {"ProductID", type text}})
in
    #"Changed Type"

Please also see the file attached.

Hi Anonymous .

First of all, I congratulate you for being an excellent tester. But then I tell you that I do not agree on the expected result that you indicate on the demo_real file. I am attaching a screen with the result that, from your previous descriptions, I would expect. I have corrected the script and am always curious to know how long it takes to work your complete file.

The script assumes that the data are sorted by date, otherwise you have to add a statement to that effect at the beginning.