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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ouafaa
Helper II
Helper II

Pre-Filtering Data before Importing

we have an sql server database that contains very old data, and we just need some recent data for creating our reports. so, we chose to import only the data whose date is later than a date X for exemple 10/12/2015 00:00:00

 

when we connect to the sql server database, and after we choose our tables and click on edit and we apply filters:

1- Are these filters applied before importing the data from the database to power Bi?

2- Or, are all our data imported first in power bi and then the filter is executed after that?

 

thanks a lot 

 

 

 

 

9 REPLIES 9
Ermanozdemir
Regular Visitor

Hello, 

Can you give me an exmaple for filtering how the SQL Statmeent look like, I am not familiar with SQL Codes so I want to ask a favor if you can share an example then I can replace the names according to my table. 

 

Thanks a lot!

Regards, 

v-yuta-msft
Community Support
Community Support

Hi ouafaa,

 

If you want to filter data before the data has been imported into PBI, you can click Get Data->advanced options and write some SQL statements to achieve data you want.

If you want to filter data in Query Editors after the data been imported into PBI, you can use query parameter to filter your table. More details about query parameter, please refer to: https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/.

 

Regards,

Jimmy Tao

If you are using the advanced feature and providing sql which contains a where clause to filter the data to your needs, powerbi will only get the results of that query.

Hi, 

 

thanks for your replies.

 

when we do et Data->advanced options and write some SQL statements to filter data, can we filter many tables on the same time ? can we write an sql statement that retrieve many tables and filter them befor importing data to power bi ?

 

Thanks

 

 

Hi ouafaa,

 

Yes, you can. But keep in mind. Power bi only support sql statement like select .. from ... where ... , don't spport stored procedure.

 

Hope this can help you.

Jimmy Tao

Hi v-yuta-msft,

 

Thanks for your reply, but I still don't know how I can do this : filter many sql server tables using advanced options sql statement befor loading those tables in different tables in power bi 

 

Could you give me an exemple of this sql statement please?

 

Thanks a lot for your help

 

Best regards,

Ouafaa

I would also like to do this. We have work orders that are linked to addresses. Im inporting two tables, workorder (for a date range) and address. The address table contains *all* possible addresses (> 2million rows) and I only want to import the ones linked to work orders for the date period selected.

 

Any help appreciated.

For future readers, you would solve this in one of two ways. If you want two tables (data sources) in your Power BI file, you'd create two separate data sources using Get Data > SQL Server database. However, if you wanted a single data source in Power BI with both workorder and address records, you'd write SQL in the aforementioned "Advanced options" when creating your data source. Your SQL might look something like:

select

   workorder.WorkOrderId,

   workorder.WorkOrderDate,

  address.AddressId,

  address.AddressData

from workorder

   left join address on address.AddressId = workorder.AddressId

where workorder.WorkOrderDate >= [someStartDate]

   and workorder.WorkOrderDate <= [someEndDate]

 

Having some basic SQL query knowledge will go a long way in solving these problems.

Anonymous
Not applicable

@ouafaa,

With SQL Server database, Power BI should be smart enough to send some transformation to the backend instead of doing the transformation in Power BI. In your case, filters will be applied first in the backend and then Power BI will retrieve the filtered rows. You can check out this article for more information regarding query folding: https://www.mssqltips.com/sqlservertip/3635/query-folding-in-power-query-to-improve-performance/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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