Forum Discussion
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.
try this using small tables initially and check run times. if it works for you with small tables and doesn't take too long, increase the size of the tables as you go.
let
Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLTM9QzMjAyUIrViVZyQhdwRhcAabFE12KJrgVDhaEhuiGGJhhqTNCNQRVxwRABm2OKoQshEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [prid = _t, date = _t]),
d=Origine[date],
all = Table.Group(Origine, {"date"}, {{"all", each _[prid]}})[all],
allprid=List.Union(all),
nprid=List.Count(allprid),
rec0=Record.FromList(List.Repeat({0},nprid),allprid),
lg=List.Generate(
()=>rec0&[n=0],
each [n]<=List.Count(all),
each Record.TransformFields(_, List.Transform(allprid, (p)=> {p, (x)=>if List.Contains(all{[n]},p) then x+1 else 0}))&[n=[n]+1],
each Record.ToTable(Record.RemoveFields(_,"n"))
),
tc=Table.Combine(lg),
#"Filtrate righe" = Table.SelectRows(tc, each ([Value] <> 0)),
ttc=Table.FromColumns({d}&Table.ToColumns(#"Filtrate righe"),{"date","grpid","idx"})
in
ttc
Hi again,
thank you very much for your help. I used the last script that you posted and it worked exactly as I need to. However, when I run it through larger dataset (15 K rows with around 1 K distinct Product IDs and 220 distinct dates) it took hours to process and unfortunately my laptop got frozen so I'll try again and leave it over night. In total, I need it to get through around 60 K rows. If it takes long but works out it's fine if I manage to set up refresh so it only goes through new data that would appear monthly (the data source is a folder where xls. files are put every month with more or less 1,5 K rows every time). Once again, thank you sooo much for your effort! I'd not be able to come up with anything close to this myself.
Honzik