Filter Rows from database Berofe Import to PowerBI

[amaleranda]

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

[v-juanli-msft]

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’) )

[amaleranda]

Ht @v-juanli-msft

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. 

[amaleranda · Accepted solution]

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