Forum Discussion
Help creating an academic year calendar in Power Query
Hi,
The solution is quite simple.
1. Load your excel table in to the power query
2. Create a custom column with following formula: (this will create a dates in your table based on your start and end date)3. Go to your new column and expand it to the rows:
4. Change Data Type of new column from Numbers to Dates
5. You have your wished calendar:
Let me know, if you solved it.
This is awesome!! Thanks so much Migasuke
I'm asking for a lot here.....
I'm just thinking of how I would add future academic years into my model.
- If I start from a table in Power Query with one date column (with dates from a given start date until today),.
- Each time I open up PowerBi, this date table will be updated such that the last date in the list is the current date.
- Each date is compared to those provided in a Master Term Dates table which gives the start and end dates of half Terms, Terms, holidays etc in a number of academic years.
- Could Power Query add columns which indicate the half Term, Term etc??
In this way, I would be able to manually edit one file with all the start/end dates of the various terms.
I hope all of this makes sense.
It's not a problem at all if this won't work.
Thanks so much again,
Ian
- Migasuke
Memorable Member
Hi,
you can definitelly automatize some of the points you mentioned above, problem is, that set up everything would require lot of rules in PQ.
In this case I would stick to the logic, which you have right now - basically you only need to maintain one excel sheet, few times per year instead of overcomplicating code.