Dynamic date table creation code

Power Query
1

Hi there,
I can’t find the dynamic date table creation coding that can be applied to the Query Editor. Can someone provide that program coding to create the date table using the dates used in table within the power bi query editor.

Thanks,
Keith

2

@Keith,

Here you go:

Extended Date Table (Power Query M function)

And here’s a great post from @Melissa on how to make the start and end dates dynamic, based on your fact table date ranges:

  • Brian
3

@BrianJ @Melissa
where do i put the factfile within the code so i don’t have to key in the parameters?

thanks

4

There’s also a video here about just this topic

Sam

5

Along with another one on this topic that just came out tonight:

  • Brian
6

Hi @Keith ,

Depends on what you want and need… Like shown in both video’s you can do this in the result Query by changing the Parameter values but you could also create a Query that doesn’t have to be invoked at all with a few adjustments.

Below, the first line of the extended Date table function.

let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonth as number, optional Holidays as list ) as table =>

If you omit that line and replace the second ‘let’ and first line below that with this code:

let
    // Enter your variables here
    StartDate = #date( 2019, 1, 1),
    EndDate = #date( 2022, 12, 31),
    FYStartMonthNo = 7,
    Holidays = null,

    // Calendar function starts here
    FYStartMonth = try if List.Contains( {1..12}, FYStartMonthNo ) then FYStartMonthNo else 1 otherwise 1,

a Date table will automatically be generated.
IMPORTANT you also have to omit the last ‘in’ clause that’s this bit.

in
fnDateTable

.
I hope this is helpful.

2 Likes
7

I will look at information that you sent.

Melissa and Sam coding for creating the date table is different. I just want to know the coding that can be put into Melissa coding without any manual keying anything in parameters. I just want to be able to look up the date from the factable and automatically put proper coding with melissa coding.

thanks @sam.mckay @Melissa @BrianJ

8

Hi @Keith

You have all the building blocks here in the thread. Please review the topic Brian referenced in the M showcase category and the other variations shown in the videos.
Update the parameters to your needs whether you go for the function or automatic generated Date table - you only have to set that up once.

If you have any trouble, I’d be happy to help just provide an overview of actual query and column names with the logic you’re looking for.

9

Hi @Melissa

I’m going to review the information and try to figure out where i need to put the factable in the logic coding that will automatically create the date table.

If i need help, I’ll let you know.

I’m going to close the thread.

thanks
Keith

1 Like