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

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"}),