Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
There are multiple ways to do it. Here are some links:
https://www.powerquery.training/portfolio/time-intelligence-with-power-query/
https://www.mrexcel.com/forum/power-bi/973390-calculate-ytd-values-power-query.html
https://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
https://www.youtube.com/watch?v=ZCxI12JB_ps
if you run into performance-problems, you might need to dig into this thread: https://social.technet.microsoft.com/Forums/en-US/1275f33f-71df-41ee-914f-c482d2f0678e/sumifs-in-pow...
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
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
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
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.
Regards,
Xiaoxin Sheng
There are multiple ways to do it. Here are some links:
https://www.powerquery.training/portfolio/time-intelligence-with-power-query/
https://www.mrexcel.com/forum/power-bi/973390-calculate-ytd-values-power-query.html
https://www.excelguru.ca/blog/2015/03/31/create-running-totals-in-power-query/
https://www.youtube.com/watch?v=ZCxI12JB_ps
if you run into performance-problems, you might need to dig into this thread: https://social.technet.microsoft.com/Forums/en-US/1275f33f-71df-41ee-914f-c482d2f0678e/sumifs-in-pow...
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
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
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"}),
Can you show me the sample data so i can take a look at it?
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |