Forum Discussion

ifillingham's avatar
ifillingham
Frequent Visitor
5 years ago
Solved

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:

 

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

 

Need Help
  • Migasuke's avatar
    Migasuke
    5 years ago

    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.



4 Replies

  • Migasuke's avatar
    Migasuke
    Icon for Memorable Member rankMemorable Member
    5 years ago

    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.



  • ifillingham's avatar
    ifillingham
    Frequent Visitor
    5 years ago

    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's avatar
      Migasuke
      Icon for Memorable Member rankMemorable Member
      5 years ago

      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.


  • ifillingham's avatar
    ifillingham
    Frequent Visitor
    5 years ago

    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