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

 

1 ACCEPTED SOLUTION
10 REPLIES 10
Resolver I
Resolver I

Can you show me the sample data so i can take a look at it?

Community Support
Community Support

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.

 

6.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Frequent Visitor

I've created the following columns in each of the calendar table and it all starts with either July or January. But, I'd like to have to start with April of each year and ends with the March of the following year. How it's possible to create a calendar for the financial year April to March 2018? 

 

If you do have any advance editor script, please share it up with me. Somehow, I've managed to find out a couple of things in fact, that didn't satisfy my hunger. It is because whenever I'd try to figure out the visualization the fiscal month numbers and fiscal quarters are not matching up. I've search out a dozens of websites and still they're not able to provide me with a proper solution. Can you please, help me up to figure it out? 

I'm not sure if I got your request right, so please share sample data of:

1) Your source data and

2) The desired result.

Thanks.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Frequent Visitor

If its just generating fiscal calendar, then, open query editor, on the left hand side go to new source, blank query and write below formula

= List.Dates(#date(2017,4,1) , 365 ,#duration(1,0,0,0))

 

I hope it helps

Frequent Visitor

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?

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Frequent Visitor

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?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

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