Hi guys
I’ve been using @Melissa date table for a million years now. Just an awesome piece of work. Not just because it “does what it says on the tin”, but it’s a good M script to get to grips with some intermediate and advanced PQ concepts.
Whilst using it I’ve had use cases to add some more cols, reduce number of cols where not required, change some of the prefixes and suffixes for client requirements. So I decided to include some of that in the core script, mainly to make me more efficient when working with clients and as an interesting PQ personal dev exercise.
Here are the changes:
-
Addition of Half year columns (calendar and fiscal)
-
Addition of further Fiscal columns to include fiscal quarter, fiscal half year together with fiscal start and end dates, offsets etc.
-
Introduce further parameters to enable options for Half year prefix (eg H or HY), Fisc Year Format (FY 23-24 or FY 2024), FY Period suffix (eg P or FP), option to append Fisc year to Fisc period/quarter or not. - helps manage non-English reports.
-
Exclusions Options to exclude data cols for Day, Week, ISO or Fiscal if not required for the PBIX file.
-
Took the liberty to change cols like [Month & Year] to [MonthYear] because the “&” was causing havoc with some custom visuals.
Whilst the end result does the job, the PQ scripting falls short of the mark, with the documentation guidance truncating because of the increased number of parameters. I’ve referred the user to the advanced editor where there is comprehensive guidance commented at the top of the script, but not ideal.
So the question for @Melissa or the PQ gurus is how to better script the function so that the documentation doesn’t truncate and how to offer drop downs on the prompts, e.g. FYFormat will be XX-YY or YYYY. Unfortunately beyond my PQ skills!
I’ve shared the code in the PBIX below for anyone interested, but really the credit goes to @Melissa
Thanks
Pete
Template.pbix (519.1 KB)
