Power BI – Filter SQL Data before import

Sometimes you need to filter the source data before loading into POWER BI Desktop. If you have a large SQL database with millions of records and you only need the records in the past few days it is not efficient to pull all the data and to filter it after. It takes a long time and it increases the Power BI service load.

So here is how you do it.

Step 1 Get Data from SQL server database and click Connect

Step 2. Type in your Server Name and Database and click on Advanced Options

Enter an SQL Statement

In the example below, I want to select from the Table “table” in the Database “database” from the server “server all the records in the past 90 days from the data “ScanDate”. Click OK and the data will be filtered and imported to the Power BI.

Here is the code as typed in the screenshot. Feel free to copy and paste it into your application.

SELECT *
FROM table
WHERE DATEDIFF(day,ScanDate,GETDATE()) between 0 and 90

That is is. Click OK and your data is filtered at the import phase. You now have a reduced dataset to work with and this will speed up your process.

For more information on how to write SQL statements to filter your data see the SQL Select Tutorial

Warning 1

Running SQL queries (not SELECT but other queries) can modify or even corrupt data in an SQL database. Use Caution when using SQL queries

Warning 2

Make sure the data you filter on is formatted the same way as the filter you are applying. For example, if you apply a “last x days” filter on a field that is formatted as Date-Time, the query may take longer than if you pull the entire data, change the format of the field and filter in Power BI.