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
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?