Extended Date Table (Power Query M function)

Hi @jgriffit,

For Sort by to work you need to identify a column at the same granularity as the column you want to sort, in this case that will be either QuarternYear OR QuarterOffset columns.

I hope this is helpful

2 Likes

Thanks @Melissa! Amazing calendar. Love it.

1 Like

You’re welcome!

:+1:

1 Like

@jgriffit,

Just FYI for future reference - I did a video on this topic last week that provides a general approach to sorting even the most difficult combinations of columns in the extended date table.

  • Brian
4 Likes

Hi @surpat. I’d suggest you remove this post and create a new topic … posting a reply to a solved thread will reduce its visibility and may lead to it taking longer for forum members to find … you can link to @Melissa’s thread instead. Please also provide your work-in-progress PBIX file and a mockup and/or Excel presentation of your desired outcome.
Greg

1 Like

Hi @surpat,

For Time Intelligence functions to work there are some requirements, you can find them in this recent post.

Do I really need a full year in my Date table? - #2 by Melissa

Alternatively I would suggest not creating a relationship between the Date- and Fact table but using TREATAS to create that relationship virtually since the granularities between the tables don’t match.

As @Greg mentioned, if you have need of further assistance please create a new thread and provide all relevant information and details.

1 Like

Thanks @Brian!

1 Like

As ever, Melissa, you take the time to explain, step by step, what and why and how. The date table as a query is wonderful and all goes to help us understand M code better. Thanks so much. Thanks to you and everyone and E-DNA, I’m moving up the ladder, rung by rung!

3 Likes

Hello Melissa,

Awesome script, but I need it in the french language. Any suggestion for me ?
I just need to translate the text nothing else.

Should I just translate the text in “qoutes” and that should work ?

Thanks

Eric (Montreal, Canada)

1 Like

Hi @ericet,

Welcome to the Forum, great to have you here.

I’m afraid not, that’s just the initial naming of the column. Thing is that once a column has been created it can be reference by its name in square brackets so “Date” becomes [Date] for example.

I think there are two ways you can approach this either copy the entire script to a text editor / visual studio code and first replace the column name like “Date” followed by a replace all of the text [Date]
OR
Keep it as is and rename all columns after you’ve invoked the function.
You can even choose to copy that Table.RenameColumns step back to the function for future use.

I hope this is helpful.

1 Like

Thanks for the quick reply, looking at the code in more détail, I do not see Monday, Tuesday, Wednesday etc for the name of the days ?

1 Like

Correct those are created with an M function, you can check in the PBIX file settings if that’s set to your region.

1 Like

Found a great YouTube with a link to download a PBIX file that has exactly what I need. So, I don’t have to reinvent the wheel. https://youtu.be/jpZ96T3wdjc

I would have prefered your calendar but time is pressing maybe someone already made different language versions of your script.

Thanks

2 Likes

GREAT!!!
I’ve been waiting for some time to come across a date table with FY details built into it…

3 Likes

Hi @Melissa. Thank you very much for this.

Could you perhaps add Fiscal Week? (please)

I know I may be pushing my (our) luck here… but could you possibly add Fiscal Week to your Power Query script? I have a solution in the form of a calculated column, but I’d really like to keep everything within one (your) script.

I’ve added “MonthInitial” and “DayOfWeekInitial”

(If you like it, feel free to add these columns to your script. I love the cleaner visuals)
– These columns sort correctly as I use zero-width invisible characters where there are duplicate values, eg “J” for Jan, June, July )

Script

script__with_MonthInitial_&_DayOfWeekInitial.txt (8.7 KB)
(this is Melissa’s script with renaming to suit me… as well as adding the two columns described above. It may be safer to just add these columns to her original script rather than use mine)

2 Likes

@michellepace Can you please share how you did that in DAX?

1 Like

Hi Antriksh (@AntrikshSharma). It’s funny you ask that. I can’t claim the fame for coming up with this, but when I first saw it - - it was in DAX – but I needed to have it in SQL. Needless to say this now comes in all three flavors because I’ve converted the original Dax to both M-Code (see script above) as well as SQL - - see here for DAX and SQL: https://community.powerbi.com/t5/Desktop/Single-letter-month-abbreviations/m-p/462132.

2 Likes

@michellepace Haha, we all learn everyday, actually for some reason my visuals weren’t grouping and showed duplicate values, entered same code again and again and then it started working, thanks for sharing !

1 Like

@michellepace,

Love that invisible characters trick to address the duplicate name issue. Saw Ferrari do that in a video last year, and thought it was one of the coolest DAX tricks I’d ever seen.

Really like the way you’ve applied it to the single letter month names.

  • Brian
1 Like

Hi @michellepace,

I’ve got solutions for the MonthInitial and DayOfWeekInitial columns.
I’ll look into the Fiscal Week requirement and get back to you, hopefully later today.

4 Likes