How to reduce the data while using "Get Data" through SQL Server

[Anonymous]

Hello,

I have a SQL Server database that has 14M rows. I would like to know if I can set criteria while uploading the data in Power BI that would only bring in data from Year 2017 to 2020.

I do see an option to write SQL queries in advance options. The column that contains year is called Fiscal_Year.

The problem is I cannot directly connect to the database. I have to choose the server first and then after completing the authentication I can choose the database.

[edhans · Accepted solution]

Do not use that Advanced Query setting. 

Just connect to the data. Then the first thing you do is filter your data as you want via the Fiscal Year field. You will notice when you do that, if you right-click the Filtered Rows step and select View Native Query, you will see Power Query is generating a SQL statement for you. You will eventually "break" query folding with some later step most likely, but that first filter will keep you with your 2017-2020 data. 

Also note that when working in Power Query, it never brings in all of the rows anyway until you do a full refresh. It only brings in 1,000 or so when you are working. It only does the full 14M rows when you load it to the data model. Or however many are in the 2017-2020 range if you keep that filter in place. In that case, SQL Server only returns the 3M rows (I made that number up) in the 2017-2020 range, not the full 14M in the entire data set.

[v-diye-msft · Accepted solution]

Hi @Anonymous 

As mentioned by @edhans ,you can directly run native database queries against the database using Power Query in your scenario. For more details about importing Data from Database using Native Database Query (Power Query), you can refer to this article.

also 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/.

[edhans · Accepted solution]

Do not use that Advanced Query setting. 

Just connect to the data. Then the first thing you do is filter your data as you want via the Fiscal Year field. You will notice when you do that, if you right-click the Filtered Rows step and select View Native Query, you will see Power Query is generating a SQL statement for you. You will eventually "break" query folding with some later step most likely, but that first filter will keep you with your 2017-2020 data. 

Also note that when working in Power Query, it never brings in all of the rows anyway until you do a full refresh. It only brings in 1,000 or so when you are working. It only does the full 14M rows when you load it to the data model. Or however many are in the 2017-2020 range if you keep that filter in place. In that case, SQL Server only returns the 3M rows (I made that number up) in the 2017-2020 range, not the full 14M in the entire data set.