Top N query with direct query

[ChrisWilliams]

Hi all,

I'm using Azure SQL and direct query.  I have a single table that has about 1.7 million rows in it and I want to offer a simple "top 10" style grid that includes a name and the value, like this:

Top 10 users:

user1      102

user2       98

etc.

I'm using the built-in Top N filter, which I create and apply to the name column.  I had anticipated that the query that goes back to Azure SQL would include a TOP N query and an OrderBy command, but that is not apparently the case.

I'm getting an error in Power BI desktop that my visualization has exceeded one million rows: 

"The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."

So, I'm assuming that PowerBI is downloading all of the 1.7 million rows to the client and then doing the Top N filtering locally.  Which is not ideal.

I know I can create a view on the server that includes the top N users, but is my understanding of that the built-in Top N filter accurate?  Does anyone have any other ideas to do Top N filters against large datasets using direct query?

[v-caliao-msft · Accepted solution]

@ChrisWilliams,

You could write query when connect to Azure SQL database where you can use top function. Or you click Edit Queries, order by one of you column, then use Keep Rows to filter top N rows.

Regards,

Charlie Liao

[Anonymous]

Hi, 

Does this always return 10 rows even if a filter is applied?
For eg, If I select a county as a filter,I want the table to show top 10 rows for that country.

Thanks

[Anonymous]

Hello everyone, I am try without success to replicate the same solution as above using AWS RedShift.  If I add the "Keep Top Rows" step in Power Query, I receive the following error in Power BI Desktop when I try to use any visualization with any field of my table.
 
OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression..
 
Without the "Keep Top Rows" step, I can successfully use a "Stacked Column Chart" visualization to show the number of entries per date in the table and the SQL query sent to the DB actually contains the limit check "select top 1000001 ...".  I would have thought that the passed SQL query would have simply contained a topn embedded in a topn like the following which runs succesfully in RedShift.

SELECT TOP 1000001 *
FROM
 (SELECT TOP 1000 "date", COUNT("date") AS "C1"
 FROM "my_table"
 GROUP BY "date")
 
Is this a bug or a limitation in the RedShift data connector for Power Query?

Thanks