Forum Discussion

Torbenan's avatar
Torbenan
Helper I
6 years ago
Solved

Filter rows in query editor - refresh slow

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 ...
Need Help
  • Torbenan's avatar
    Torbenan
    6 years ago

    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's avatar
Greg_Deckler
Community Champion
6 years ago

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's avatar
    Torbenan
    Helper I
    6 years ago

    Thanks - I aksed the DB admin to create the view