Power Query Calendar Table - including Financial & Non-Standard Calendars
Vložit
- čas přidán 2. 07. 2024
- ★ Want to automate Excel? Check out our training academy ★
exceloffthegrid.com/academy
★ Get the example file ★
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-...
File Reference for this video: 0215 PQ Calendar Table.zip
★ About this video ★
Calendar tables are commonly used in Excel and Power BI. We can generate them using Power Query.
In this video we create a basic table, then take it too the next level with (a) dynamic dates (b) financial periods (c) non-standard calendar dates.
INCLUDES:
0:00 Introduction
0:27 Basic calendar table
3:28 Dynamic dates
4:33 Financial periods
6:22 Non-standard calendars
8:19 Wrap-up
★ Download 30 most useful Excel VBA Macros ebook for FREE ★
exceloffthegrid.com/
★ Where to find Excel Off The Grid ★
Blog: exceloffthegrid.com
Twitter: / exceloffthegrid
#MsExcel
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-signup/
File Reference for this video: 0215 PQ Calendar Table.zip
You are good, both in choosing the topic and make them easy to implement
Thank you
Thank you, that's very kind of you to say.
Very informative video
Awesome Level of teach and special thanks
Thanks - I'm glad you like it. 😁
Fantastic video as always! Your PQ vids have been a lifesaver for my occasional large data tasks, from a crawl to swift delivery. ⚡️👍🏾
That's great news. I'm glad to help.
fantastic one! You could also have worked on Quarters for by CY and FY... that would of great use!!
We would just use Date.QuarterOfYear instead of Date.Year or Date.Month.
I've worked in some large companies and interestingly, quarter has never been a requirement. So, it never really comes to my mind; but I know others use them a lot.
Excelente video… 👍🏽
Thanks 😁
Thanks for the video !
I personally perfer the smaller m-code
{ Number.From( StartDate ) .. Number.From( EndDate ) }
I hope the next video is about Time Table
Yeah, that works. I don't have a big preference wither way.
In our membership, we've got some custom functions in the Functions Library to deal with Time Tables and grouping into time segments. But at the moment I've not got any plans for a video.
I have an issue I’ve been trying to solve and I’m hoping you can provide a direction. I have a table of employees with a start date and an end date. Current employees don’t have an end date. I need to calculate the number of active employees per month for the last 3 years. I’ve tried to use a join the data to a date table and use sumx and collect as a measure to calculate it, but since there isn’t a date for each month that there been employed it’s only counting each employee the first month they’re employed. Any thoughts?
It sounds like you need to use CALCULATE, using the dates and blank to change the filter context.
@@ExcelOffTheGrid thank you. I’ll give that a shot.
We use 4,4 ,5 - your solution is good for a single year, not so good with a large date range - Had hoped it was going to use equivalent of Excels weeknum and type 21 - that's been stumping me a bit
Why won't it work for a large date range? You could easily create it for the next 20 years. How far do you need to go out?
HOW DID YOU CREATE THE Source CODE FOR THE TWO TABLES? DO YOU HAVE A FUNCTION YOU COULD SHARE? PLEASE???!!!
Two comments. The start date must always be January 1 of the first year and the end date must always be December 31 of the last year. To get this I chose to use:
StartDate = #date(Date.Year(List.Min(Transactions[Date])),1,1),
EndDate = #date(Date.Year(List.Max(Transactions[Date])),12,31),
Also, after the Fill Up operation, it's a LOT easier to filter null in the Year column than the Date column, especially for a calendar for more than one year.
Still a valuable video!
The Tables were created with Enter Data. But could come from anywhere.
You can find them in the Example file - which is available in our free Insiders program.
Is there an advantage to creating the date table in power query rather than power pivot? Are there pro's and con's for one way over the other?
In my opinion, a date table is the same as every other dimension Table. Therefore we should load it into the data model in the same way.
A PQ calendar table is also more dynamic (I.e creating dates based on the data).
Ultimately, there is probably little difference. But I prefer the PQ option.
@@ExcelOffTheGrid thanks for the answer 🙂 yeah I have had issues in the past sometimes creating the date table in power pivot with it picking up a date column that I actually want it to ignore. I'd never considered building the date table using power query so I'll definitely give this a go! Thanks again.
Hi Mark.
I really enjoyed the video and it will come in very useful.
I see your dates in the calendar are in UK format.
Can you help me an issue I appear to have suddenly noticed only this week?
When I have dates in an Excel sheet or csv file in UK format they are now always pulling into Power Query in US format.
I have tried using the Local option, but as soon as I change Type back to Date I then get an error for any date with the day greater than 12. This was working fine just last week. My Excel date setting is UK and so is my Windows settings.
I can't think of any other reason this has started happening.
Regards,
Alan
Is it in on workbook or all workbooks?
@@ExcelOffTheGrid It is now happening on all workbooks. I ended up trying a Quick Repair of Office 365 and it won't finish the repair, so possibly something in my system has become corrupt. I'll try the usual repair/restore options over the weekend and report back as this behaviour is odd.
Sorry Mark, I realise this isn't a help desk, but I thought I was missing a setting somewhere.
Regards,
Alan
@@ExcelOffTheGrid Just to follow up. After doing a system restore, all is working again and I am getting the correct date format. I can delete my posts if you wish.
Regards,
Alan
Thanks for keeping me up-to-date. That is a very odd scenario. Well done for fixing it.
Get an error EVERYTIME i try to enter this. I am using Office 365 Power Query. I have written it 4 times EXACTLY as you show and everytime I hit the DONE button I get this error "An error occurred in the ‘’ query. Expression.Error: The name 'Startdate' wasn't recognized. Make sure it's spelled correctly."
Any idea, I give up on learninig if I get unexplained arrors all the time.
Power Query is case sensitive. So your variable is probably called StartDate, but then you’ve entered Startdate when you have tried to use it.
That is my guess.
@@ExcelOffTheGrid FOUND IT. I didn't know it was case sensative. Once I fixed that it works. I have a lot to learn about PQ. But I plan on learning it. I can see where its power can be a GREAT addition to Excel. Thanks for the help.