Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I hope someone can help me out with making an academic year calendar using Power Query rather than PowerBI and Dax. The reason for using Power Query rather than DAX is to limit any impact (of creating the academic calendar in DAX) on the end user when they interact with the dashboard.
I have a Master Term Dates file with the format as in the image below:
And a calendar like below in Power Query
My calendar needs to includes columns showing the relevant half-term, term and academic year information indicated by the start and end dates.
My ideal output would be:
For dates between 5/11/2018 and 21/12/2018, The columns would look show:
The Term and Academic Year are the same but the Half Term is different!
I know I need some kind of merge table function but this is beyond me at the minute.
I hope all of this makes sense!!
Thanks so much,
Ian
Solved! Go to Solution.
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.
Thanks so much,
When i thought about things a bit more, the calaendar will be recreated every time the model is refreshed.
Just want to say a massive thank you for answering my question. You genuienly have saved me hours of work!!
Best wishes,
Ian
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.
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.