Forum Discussion
Filter Rows from database Berofe Import to PowerBI
Hi
Hope someone can guide me.
I have a table stored in azure with 5 rows(example only). from that tabel I need to import only 3 rows. FIlter is based on one or more colums see below
Table in Azure (SQL)
| Col1 | Col2 | Col3 | col4 |
| 1 | Apple | Summer | Hot |
| 2 | Apple | Summer | cold |
| 3 | Banana | Summe | Hot |
| 4 | Mango | Winter | cold |
| 5 | Fruit | Winter | hot |
Output table in PBIX file
| Col1 | Col2 | Col3 | col4 |
| 1 | Apple | Summer | Hot |
| 2 | Apple | Summer | cold |
| 3 | Banana | Summe | Hot |
Criteria here is filter for rows in Col3 to be Summer.
I want filtering to be happen before import. I understand that I can filter rows arter I import all 5 rows to the PBIX file. Case here is my tables have more than 50million rows.
PS:also need to know how to add Col4 for the same filter
Below code would work in if your data alredy in PowerBI
let
Source = Sql.Database("SourceName", "DatabaseName", [Query="SELECT *#(lf)FROM [AHS].[Application]#(lf)WHERE [Application Ref] = '148'"])
in
Source
3 Replies
Hi amaleranda
If you want to filter data before the data has been imported into PBI, you could click Get Data->advanced options and write some SQL statements to achieve data you want.
Example sql statement
SELECT * FROM table WHERE (Col3 in(' Summer' , ’ Summe’) )Thanks a lot for the reply
My data model is already in the PowerBI( so I cannot use Get Data). I am going to additional row into the related sql tablse.
I do not want those additional rows in my current PowerBI data model.
For instance my current Azure SQL table(TblA) has 10 row at the moment. Those 10 rows are in my current PowerBI Data model.
I am in the proces of adding another 5 rows to the TblA to make it 15 rows. From those 15 rows I only need 11 rows in my current PowerBI Data model.
I know I can filter out data from PowerBI data model using a simple column filter. I just want to stop getting additional columns (exclude 4 frolums) when import job is happening.
Below code would work in if your data alredy in PowerBI
let
Source = Sql.Database("SourceName", "DatabaseName", [Query="SELECT *#(lf)FROM [AHS].[Application]#(lf)WHERE [Application Ref] = '148'"])
in
Source
