cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ifillingham
Frequent Visitor

Help creating an academic year calendar in Power Query

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:

ifillingham_0-1625332340016.png

 

And a calendar like below in Power Query

ifillingham_1-1625332538240.png

 

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:

ifillingham_2-1625333030925.png

 

For dates between 5/11/2018 and 21/12/2018, The columns would look show:

ifillingham_3-1625333226334.png

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

 

1 ACCEPTED SOLUTION
Migasuke
Responsive Resident
Responsive Resident

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)

Migasuke_0-1625334668929.png

3. Go to your new column and expand it to the rows:

Migasuke_1-1625334794384.png

4. Change Data Type of new column from Numbers to Dates
5. You have your wished calendar:

Migasuke_2-1625334935895.png


Let me know, if you solved it.



View solution in original post

4 REPLIES 4
ifillingham
Frequent Visitor

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

ifillingham
Frequent Visitor

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
Responsive Resident
Responsive Resident

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.


Migasuke
Responsive Resident
Responsive Resident

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)

Migasuke_0-1625334668929.png

3. Go to your new column and expand it to the rows:

Migasuke_1-1625334794384.png

4. Change Data Type of new column from Numbers to Dates
5. You have your wished calendar:

Migasuke_2-1625334935895.png


Let me know, if you solved it.



View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors