Date table

[mandar]

Hi, I want your opinion on what is the best way to call a date table in Power Query (Imported data set)

1. Hard code the date in Snowflake query; say starting from 2020

2. Edit the query in Snowflake to pick (todays date - no of years) Dynamic

3. Create a dynamic date table in PQ by referencing dates in the fact table

I would love to know why; especially 1 vs 2  🙂

Also, what is considered best practice in the above situation.

[amitchandak]

@mandar . SQL is allowed in Snowflake for some time, you can sql that will give you data for 2020

Open power query in advance editor you will see SQL in a string

break that use year from M parameter or based on DateTime.LocalNow()

example

"Select * from sales where year(Date) = " & MParamYear ,

or

"Select * from sales where year(Date) = " & Number.ToText(Date.Year(Date.From(DateTime.FixedLocalNow())) )

Or you can add a filter, as we do in case of incremental load.