Change M Code in Date Table

[GuyJohnson]

When using Melissa’s Date Table M Code is there a way to change the DayInWeek column so that Monday is day 0

I’m trying to track payroll information that runs Monday to Sunday and want to use a slicer based on the end of week date.

In addition I have noticed that when I copy her code into my model all the Friday and Saturday dates are missing from the calendar.

Help please

Guy

[Melissa]

Hi @GuyJohnson,

If you are experiencing difficulties please provide a sample PBIX file that illustrates and/or reflects this.

The (WDStartNum) variable allows you to set the default weekday numbering from 0-6 to 1-7. The default being 0-6.

I trust you’ve copied the latest version from here…

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

[GuyJohnson]

I changed my Date Table to the one you jus supplied and all the entries were there. I guess my copy got corrupted somehow, it happens.

I am unable to display my payroll in a table that lists as follows:

Monday, Tuesday, Wednesday,… Sunday

Guy

[Melissa]

Hi @GuyJohnson,

Glad to hear that resolved some of the issues.

You should be able to sort DayOfWeekName by DayOfWeek number, to display them in the correct order as illustrated below.

Here’s a sample file.
WeekEnding Sort on DayOfWeek.pbix (120.6 KB)

I hope this is helpful

[GuyJohnson]

@Melissa Good Morning. Thanx for the suggestion but the sort order suggested was already in place and it doesn’t work. I’m attaching my PBIX file (it is a work in progress).

On page 2 the lower 2 tables have the same data with different column headers but they display differently.

I have to be making a basic error but I don’t see it.

Thoughts???

Payroll for eDNA Assistance.pbix (147.5 KB)

Guy

[Melissa]

Hi @GuyJohnson,

Thanks for adding a sample fille!
Made a modification to the code. Give this a go.

Payroll for eDNA Assistance.pbix (146.0 KB)

I hope this is helpful

[GuyJohnson]

@Melissa That works fine, Thanx.

What code was modified?? Is this something I overlooked or changed??

Guy

[Melissa]

Hi Guy,

No it was a section in the date table query itself. The code has been updated now in both the linked post above & the analyst hub. So everyone can benefit from this improvement.

Thanks for reporting it.

[GuyJohnson]

Melissa, Thanx a lot for your quick responses to my issue.

Have a good week.

Guy