Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jbluhm
Frequent Visitor

Date Filter within Power Query?

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?

2 REPLIES 2
TomMartens
Super User
Super User

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
 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors