cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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 10'000 give back the name of the customer, otherwise "Other customers"..

 

Regards
Matt

 

10 REPLIES 10

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors