Filter rows in query editor - refresh slow

[Torbenan]

In a Azure SQL table I have 12 million rows. The table has a column called IntelvalType, and I only need rows where the IntervalType = 8. This is about 177.000 rows.

Refreshing data takes a long time. I seems like every 12m rows are download to Power Bi desktop, the evaluated against the row filter. Am I correct in this assumtion? I there a better way to get the rows I need and only the rows I need?

[ibarrau]

Hi. You have to consider that power bi won't add additional load time unless you have a lot of crazy power query steps. Consider running a SELECT * FROM table WHERE IntervalType = 8 in the engine with Management Studio or Data Studio. Then that should be the time that will take the power bi service to refresh directly without a gateway.

About Power Bi Desktop you are right, it will download all the table compressed. That is why it can take a bit longer than the time of the query result directly on the engine.

The important thing is that once you have this published on service you don't need a gateway and it should take almost the same time of the query against the engine.

Hope this makes it clearer.

[Torbenan · Accepted solution]

The problem has been solved in Power Bi desktop, since the DBA didn't want a extra view in the DB.
In stead of doing the filtering in Power Query, I did the following.
Created a new data source Power Bi Desktop
Enter Database Names and Server

Selected advanced mode.

wrote the select statement.
I did not time it by a stop watch, but I think data refresh time went from 30 min to 30 sec.
I found the inspiration to the fix here: https://www.youtube.com/watch?v=EhGF372t0sU

[Greg_Deckler]

I would highly recommend creating a view in your SQL table that does the filtering and pull from that. Or create the SQL query to do the filtering and in the Advanced part of connecting to your server, paste in the SQL code.

[Torbenan]

Thanks - I aksed the DB admin to create the view