Forum Discussion
year to date in query editor
There are multiple ways to do it. Here are some links:
https://www.powerquery.training/portfolio/time-intelligence-with-power-query/
https://www.mrexcel.com/forum/power-bi/973390-calculate-ytd-values-power-query.html
https://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
https://www.youtube.com/watch?v=ZCxI12JB_ps
if you run into performance-problems, you might need to dig into this thread: https://social.technet.microsoft.com/Forums/en-US/1275f33f-71df-41ee-914f-c482d2f0678e/sumifs-in-power-query-rolling-12-months?forum=powerquery
HI Anonymous,
I'm not so sure for your requirement, can you share some detail contens about this?
In addition, if you want to convert year to date, you can try to add a static date and use date.From function to convert this column.
Regards,
Xiaoxin Sheng
- ImkeF
Community Champion
There are multiple ways to do it. Here are some links:
https://www.powerquery.training/portfolio/time-intelligence-with-power-query/
https://www.mrexcel.com/forum/power-bi/973390-calculate-ytd-values-power-query.html
https://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
https://www.youtube.com/watch?v=ZCxI12JB_ps
if you run into performance-problems, you might need to dig into this thread: https://social.technet.microsoft.com/Forums/en-US/1275f33f-71df-41ee-914f-c482d2f0678e/sumifs-in-power-query-rolling-12-months?forum=powerquery
I am working with approximately 60,000 rows, have tried all of the recommended methods, and they are all quite slow. In Excel, it is simply a calculated column: =IF(M2<M1,F2,N1+F2) with column M the index, column N the running total, and column F the data to be totaled. Of course, that runs in milliseconds (It would be nice to have the ability to code an excel formula in a Power Query column that actually acted like a formula).
The fastest appears to be your solution at excelguru.ca, using recursion - but I have one issue that I can't solve - that being that I need to have a running total based on an index that I created (from 1) via a group by, so it resets to 0 when the index resets.
This was your code from that site:
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
ChangedType = Table.Buffer(Table.TransformColumnTypes(Source,{{"Date", type date}})),
Iterate = List.Buffer(List.Generate(
()=>[Counter=0, Value_=ChangedType[Sale]{0}],
each [Counter]<=Table.RowCount(ChangedType),
each [Counter=[Counter]+1,
Value_=[Value_]+ChangedType[Sale]{[Counter]+1}],
each [Value_])),
Table = Table.FromColumns({ChangedType[Date], ChangedType[Sale], Iterate}),
Rename = Table.RenameColumns(Table,{{"Column1", "Date"}, {"Column2", "Sale"}, {"Column3", "CumSale"}}),
RemError = Table.RemoveRowsWithErrors(Rename, {"CumSale"})
in
RemErrorAny recommendation on how would I reset the running total on an index reset?
- ImkeF
Hi DThayer,
you should definitely apply the running total at the group-level before you expand it if you want to run it fast.
So transform the query to a function like this:
(Source as table) =>
//Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content], ChangedType = Table.Buffer(Table.TransformColumnTypes(Source,{{"Date", type date}})), Iterate = List.Buffer(List.Generate( ()=>[Counter=0, Value_=ChangedType[Sale]{0}], each [Counter]<=Table.RowCount(ChangedType), each [Counter=[Counter]+1, Value_=[Value_]+ChangedType[Sale]{[Counter]+1}], each [Value_])), Table = Table.FromColumns({ChangedType[Date], ChangedType[Sale], Iterate}), Rename = Table.RenameColumns(Table,{{"Column1", "Date"}, {"Column2", "Sale"}, {"Column3", "CumSale"}}), RemError = Table.RemoveRowsWithErrors(Rename, {"CumSale"}) in RemErrorand apply it in a Table-AddColumn-step, referencing the "All"-column from your grouped table.
Also no need to reset when Index=0.
