Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

year to date in query editor

Hello   Is it possible make a year to date column in query editor? I need the year to date column for a conditional column where would do something like that: if customer turnover is bigger than...
Need Help
Show and Tell
DThayer's avatar
DThayer
Frequent Visitor
8 years ago

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
RemError

 

Any recommendation on how would I reset the running total on an index reset?

ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
8 years ago

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
RemError

and apply it in a Table-AddColumn-step, referencing the "All"-column from your grouped table.

 

Also no need to reset when Index=0.

 

  • DThayer's avatar
    DThayer
    Frequent Visitor
    8 years ago

    Almost there - Here is my function named fnAddRunningTotal:

     

    (MyTable as table) as table =>
    let
        Source = Table.Buffer(MyTable),
        ChangedType = Table.Buffer(Table.TransformColumnTypes(Source,{{"OrderDate", type date}})),Iterate = List.Buffer(List.Generate(
        ()=>[Counter=0, Value_=ChangedType[Orders]{0}],
        each [Counter]<=Table.RowCount(ChangedType),
        each [Counter=[Counter]+1,
        Value_=[Value_]+ChangedType[Orders]{[Counter]+1}],
        each [Value_])),
        Table = Table.FromColumns({ChangedType[OrderDate], ChangedType[Orders], Iterate}),
        Rename = Table.RenameColumns(Table,{{"Column1", "OrderDate"}, {"Column2", "Orders"}, {"Column3", "CumOrders"}})
    in
        Rename

     

    Here is the code calling it:

     

        #"Sorted Rows1" = Table.Sort(#"Added Custom7",{{"MainOffer", Order.Ascending}, {"Channel", Order.Ascending}, {"OrderDate", Order.Ascending}}),
        #"Grouped Rows3" = Table.Group(#"Sorted Rows1", {"MainOffer", "Channel"}, {{"Data", each _, type table}}),
        #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows3", "CumOrders", each fnAddRunningTotal([Data]))
    in
        #"Invoked Custom Function"

     

    I am getting 2 tables, one from the Table.Group, and one from the Table.AddColumn.  Both look right, but how do I get the added column in the first table?

    • DThayer's avatar
      DThayer
      Frequent Visitor
      8 years ago

      I got this to work.  I had a misunderstanding about how it works - I thought that this simply added a column to the existing table.  It does not - you have to recreate the table (all columns) plus the calculated running total.  The last 2 lines of the function (Table.FromColumns and Table.Reb=nameColumns) need to be modified to suit your input.  I had 10 columns.  The cumulated number is then appended - named "Iterate".  Here is my final function:

       

      (MyTable as table) as table =>
      let
          Source = Table.Buffer(MyTable),
          ChangedType = Table.Buffer(Table.TransformColumnTypes(Source,{{"OrderDate", type date}})),Iterate = List.Buffer(List.Generate(
          ()=>[Counter=0, Value_=ChangedType[Orders]{0}],
          each [Counter]<Table.RowCount(ChangedType),
          each [Counter=[Counter]+1,
          Value_=[Value_]+ChangedType[Orders]{[Counter]+1}],
          each [Value_])),
          Table = Table.FromColumns({ChangedType[ChannelTotalOrders], ChangedType[OfferTotalOrders], ChangedType[Division], ChangedType[OrderDate], ChangedType[Orders], ChangedType[DaySorted], ChangedType[DateWithDay], ChangedType[InHomeDate], ChangedType[ElapsedDays], ChangedType[ElapsedWeeks], Iterate})
      in
          Table

       

      Finally, to call this from your query, use the following with proper naming modifications:

       

          #"Grouped Rows3" = Table.Group(#"Sorted Rows1", {"MainOffer", "Channel"}, {{"Data", each _, type table}}),

          #"AddedRunning" = Table.TransformColumns(#"Grouped Rows3", {"Data", each fnAddRunningTotal(_)}),
          #"Expanded Data3" = Table.ExpandTableColumn(#"AddedRunning", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11"}),