Date Filter within Power Query?

[jbluhm]

I have a report that I need to connect to my SQL sever with a date range for the current year to the end of last month? How would I filter that? I think Power Query would probably be the most intuitive but unaware of any other options?

[TomMartens]

Hey @jbluhm ,

assuming that the SQL Server tables have date column that enable the filtering you can use this snippet inside each Power Query query to filter the rows of the table accordingly

//creates a datetimevalue that marks the beginning of the current year
    StartOfYear = 
        Date.StartOfYear(
            DateTime.FixedLocalNow()
        )
    //creates a datetimevalue that marks the beginning of the current month    
    , StartOfCurrentMonth = 
        Date.StartOfMonth(
            DateTime.FixedLocalNow()
        )
    //filters the table from the beginning of the current year to the end of previousyear using less than the beginning of the current month     
    , #"Filtered Rows" = 
        Table.SelectRows(
            #"YourTableStep"
            , each [DateKey] >= StartOfYear and [DateKey] < StartOfCurrentMonth
        )

What happens is this:
Two date values are created one, stores the beginning of the current year and the 2nd the beginning of the current month. I consider the condition "less than the start of the current month" more robust finding everything in the previous month because the precision of date/datetime values needs not to be considered.
Then both values are used inside the function Table.SelectRows() to filter the rows of the table accordingly. Here the column of the SQL Server table that contains the date/datetime value is called "DateKey".

Hopefully, this provides what you are looking for to tackle your challenge.

Regards,

Tom
 

[jbluhm]

Thank you!