Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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

Newbie1
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

v-shex-msft
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.
DThayer
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

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

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

Hasan
Resolver I
Resolver I

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.