School term week number

Power Query
1

Hello,

Schools count their weeks from the beginning of terms. I’d like to add a week number to the basic date table that resets at the start of each term. I’m looking for something like this:

image
image466×518 6.19 KB

My term dates table looks like this:
image

Any help to get me started would be greatly appreciated.
Kind regards, Dale

2

Hi @dalef,

Welcome to the Forum!

I’d be happy to look into that with you so could you supply the term dates table data and confirm are you referring to this basic date table?

Thanks!

1 Like
3

Hi Melissa,
Yes, that’s the table I’m using.
Regards, Dale
Sorry, here’s the term dates file.
terms.csv (443 Bytes)

4

Hi @dalef,

Quick question since the date ranges are non-contiguous between Terms and/or Semesters, so what value should be given to a date that doesn’t fall between the Start- and End Date?
A null or …?

Let me know.

5

They could be called “Holidays” if that is possible.
Thanks, Dale

1 Like
6

Hi @dalef,

Thanks for the quick reply! :+1:

Just to note that would mean a data type change from integer to text string (not optimal). Alternatively we could assign those days a 0 (zero) to indicate these are holidays. Would that be acceptable? If not that’s no problem.

7

No problem Melissa, a 0 would be fine too.

8

Hi @dalef,

So this is what I’ve got for you.
Created a separate fxCreateTermTable function

( Semester as number, Term as number, StartDate as date, EndDate as date ) as table =>
let
    DayCount = Duration.Days( EndDate-StartDate )+1,
    DateList = List.Dates( StartDate, DayCount, Duration.From(1) ),
    AddPeriodName = List.Transform( DateList, each { Semester, Term, _ } ),
    CreateTable = #table( type table[Semester=number, Term=number, Date=date], AddPeriodName),
    AddWeek = Table.AddColumn(CreateTable, "Term Week", each ((Number.From( Date.StartOfWeek([Date], 1)) - Number.From(Date.StartOfWeek(CreateTable[Date]{0}, 1))) /7) +1, type number)
in
    AddWeek

When invoked this returns a TermsTable support query.

image

Then you could merge this with the Dates table and add a “Replace values” step to swap out the nulls with 0’s.

Alternatively I’ve created a fxCalendarTerms function that does this for you BUT that requires a table called: Term which needs to include these field (column) names AND requires these data types.

image

Else you will have to modify the code highlighted here and/or convert datetime values into date datatypes by wrapping them in Date.From

image
image856×190 17.3 KB

.

You will find both methods and some other supporting queries in the attached PBIX

image
image1352×332 30 KB

eDNA - Basic Date Table with Terms.pbix (134.7 KB)

I hope this is helpful

2 Likes
9

That is going to be so helpful Melissa. I’m sure a few other school admins will make use of this. Thank you so much.
Regards, Dale

10

Hi @dalef, welcome to the Forum! :slight_smile:

It’s great to know that you are having a great experience so far and that your inquiry was answered immediately.

Please take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum.

Also, if your question was answered, kindly mark the response that helped you solved your inquiry as SOLUTION. :slight_smile: