Convert Dates to Fiscal Periods with Power Query - Better than Formulas! - My Online Training Hub
video description
Date: 2022-04-08
Related videos
Comments and reviews: 10
Peter
As always I enjoyed your video and agree that the best time for cleaning and sorting data is before it ever gets to the Excel workbook.
That said, I wouldn't regard the processing involved in Power Query as in any way trivial. I am not sure that a user that struggles with a VLOOKUP is going to do so much better distinguishing their left outer join from an inner join. You are now so familiar with the environment that you handle the advanced editor with flair and fluency but I assure you I am far more laboured.
For the Aussie FY, I reverted to a single formula
= LET(-
oldFY?, MONTH(date) < MONTH(startFY),-
FiscalMonth, 1 + MONTH(date) - MONTH(startFY) + 12-oldFY?,-
Quarter, 1 + QUOTIENT( FiscalMonth-1, 3 ),-
FY, YEAR(date) - oldFY?,-
CHOOSE(-1,2,3-, FiscalMonth, Quarter, FY ) )
It just got me to the point of deciding that accountancy and tax are incomprehensible. In the UK our fiscal year starts on 6 April, so how many days are there in March? Or is April 3 part of month 13?
reply
As always I enjoyed your video and agree that the best time for cleaning and sorting data is before it ever gets to the Excel workbook.
That said, I wouldn't regard the processing involved in Power Query as in any way trivial. I am not sure that a user that struggles with a VLOOKUP is going to do so much better distinguishing their left outer join from an inner join. You are now so familiar with the environment that you handle the advanced editor with flair and fluency but I assure you I am far more laboured.
For the Aussie FY, I reverted to a single formula
= LET(-
oldFY?, MONTH(date) < MONTH(startFY),-
FiscalMonth, 1 + MONTH(date) - MONTH(startFY) + 12-oldFY?,-
Quarter, 1 + QUOTIENT( FiscalMonth-1, 3 ),-
FY, YEAR(date) - oldFY?,-
CHOOSE(-1,2,3-, FiscalMonth, Quarter, FY ) )
It just got me to the point of deciding that accountancy and tax are incomprehensible. In the UK our fiscal year starts on 6 April, so how many days are there in March? Or is April 3 part of month 13?
reply
Peter
Hi Mynda. I have some words to eat! I worked through the Power Query and was fascinated by the way you used the outer join to combine two dissimilar datasets in order to achieve the equivalent of an approximate lookup. I would have claimed that ordered lists such as time sequences should be treated as array problems and not list processing, yet you managed the task with some elegance!
That said, INDEX/XMATCH returned all the values with a single array, XLOOKUP was elegant if one is prepared to accept a single row formula copied down. The next shock for me was that VLOOKUP, a function that I have despised for years, also did a pretty good job
= VLOOKUP(Table2[-Date], Periods_454, -1,2,3,4-)
How am I supposed to hang on to my prejudices when you insist on demonstrating other possibilities? :-)
reply
Hi Mynda. I have some words to eat! I worked through the Power Query and was fascinated by the way you used the outer join to combine two dissimilar datasets in order to achieve the equivalent of an approximate lookup. I would have claimed that ordered lists such as time sequences should be treated as array problems and not list processing, yet you managed the task with some elegance!
That said, INDEX/XMATCH returned all the values with a single array, XLOOKUP was elegant if one is prepared to accept a single row formula copied down. The next shock for me was that VLOOKUP, a function that I have despised for years, also did a pretty good job
= VLOOKUP(Table2[-Date], Periods_454, -1,2,3,4-)
How am I supposed to hang on to my prejudices when you insist on demonstrating other possibilities? :-)
reply
Jerry
Great technique & video!
In the Periods_454 query, the Fiscal Qtr column data type was changed from Any to Whole Number, however if you then change the values in that table from numbers (1-4) to Quarters (Q1-Q4), the query has an error. Removing ---Fiscal Qtr-, type text-- returns the column data type to -any- which allows it to be a number if no Q is present, or text when there is. Changing the data type to Text forces a single digit entry to be loaded as Text.
reply
Great technique & video!
In the Periods_454 query, the Fiscal Qtr column data type was changed from Any to Whole Number, however if you then change the values in that table from numbers (1-4) to Quarters (Q1-Q4), the query has an error. Removing ---Fiscal Qtr-, type text-- returns the column data type to -any- which allows it to be a number if no Q is present, or text when there is. Changing the data type to Text forces a single digit entry to be loaded as Text.
reply
Shaun
Hi Mynda, your tutorials are just on another level. That said I'm trying to achieve something a little unique and does not quite fit into the boxes of the tutorials you've given or I simply don't know how to apply them correctly...probably the latter. Can you tell me if you offer private hours and if so could you advise what you need in return? Please accept my apologies for this public request but I did not have any other contact options available to me.
reply
Hi Mynda, your tutorials are just on another level. That said I'm trying to achieve something a little unique and does not quite fit into the boxes of the tutorials you've given or I simply don't know how to apply them correctly...probably the latter. Can you tell me if you offer private hours and if so could you advise what you need in return? Please accept my apologies for this public request but I did not have any other contact options available to me.
reply
vishnu
Mynda, I have a question, my Purchase orders(PO) spread over financial years, mostly over two but a few of them for 3 , 4 or 5 years also, I want to calculate the number of dates each PO is active in each financial year and then accordingly assign spend to each year assuming spend is evenly spread for each day of the PO, please guide with this query.
reply
Mynda, I have a question, my Purchase orders(PO) spread over financial years, mostly over two but a few of them for 3 , 4 or 5 years also, I want to calculate the number of dates each PO is active in each financial year and then accordingly assign spend to each year assuming spend is evenly spread for each day of the PO, please guide with this query.
reply
yes
Hi mam, in matrix visual based on the selection in the slicer I need to see the change in the column names(headers) dynamically.. can we achieve this by using Dax measure ?
reply
Hi mam, in matrix visual based on the selection in the slicer I need to see the change in the column names(headers) dynamically.. can we achieve this by using Dax measure ?
reply
Jaimal
HI mynda
my formula is correct and when i click ok, i receive some errors saying -We cannot apply operator + to types Text and Number.- (my formula is right though)
reply
HI mynda
my formula is correct and when i click ok, i receive some errors saying -We cannot apply operator + to types Text and Number.- (my formula is right though)
reply
Terry
Great tutorial Mynda - Thanks. BTW do you know if is it possible to calculate age (in years) in Power Query that correctly accounts for leap years (like DATEDIFF)?
reply
Great tutorial Mynda - Thanks. BTW do you know if is it possible to calculate age (in years) in Power Query that correctly accounts for leap years (like DATEDIFF)?
reply
Richard
Hi Mynda, this is exactly what I have been looking for. If I use these to create a model for my users, will they need Power query enabled to use it?
reply
Hi Mynda, this is exactly what I have been looking for. If I use these to create a model for my users, will they need Power query enabled to use it?
reply
Krish
Thank you very much for your videos. Is there a way to reduce files size using power query? I am having trouble to share 100mb files
reply
Thank you very much for your videos. Is there a way to reduce files size using power query? I am having trouble to share 100mb files
reply
Add a review, comment
Other channel videos
