Forum Discussion

mandar's avatar
mandar
Frequent Visitor
3 years ago

Date table

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.

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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.