Sorting a large table in Power Query. Best approach?

[yyyy]

I have a dataset with the following columns: 

date (datetime) 
categoryID (20 different ID's numbered 1 to 20) 

amount (decimal value) 

Number of rows in the table; 5 million. 

In Power Query, I sort the table (by categoryID, then by date/time) and then calculate the duration of time (difference between the date in the current row and the previous row). 

This works good when I filter the imported data first to max 200.000 rows.   (less than one minute to load 200.00 records and to sort the table) 
But when I import all 5 million records, then it takes too much time (less than 3 minutes to load the data, but then the sorting takes very very long. (>60 minutes). 

Sorting the table is done with table.sort. 

Is there a way to improve the performance of sorting, so that it runs much faster? 

Details: 

Datasource = dataverse; I use a sql server connection to connect. I already tried to sort the table with the sql query, but 'Order by' is not allowed to use unfortunatelly. So that's not possible. 

Looking forward to your response, 

Thanks!

[sroy_16]

If you are not allowed to use ORDERBY in the SQL side try creating a view of the same.
Try using this code.
CREATE VIEW SortedData AS
SELECT *
FROM YourTable
ORDER BY categoryID, date

Use this view in your PowerBI instead of the raw.

[MFelix]

Hi @yyyy,

When you refer that the sorting works slow is it on the Desktop or on the Service?

If it's on the Desktop the fact that you are pulling 5M rows does not work in your favour, a good practice while developing this type of semantic model is to have a parameter to filter out the data that way while you are on the desktop you work with a sample of the real data getting things to load quickly and on the service you just change the parameter to do the full refresh.

[yyyy]

Thanks for the tip regarding using a parameter. I tried to use it, but it doesn't work as expected. 
A simple example of my power query script: 

In this script you see 'LimitRows' which is the parameter that I created. 

Paramater: "LimitRows"  (decimal value, default value = 5) 

In Power BI Desktop this works as expected. When I change the parameter to 100, it results in 100 rows and so on. 
When I publish the report to Power BI Service (with default value 5 for parameter LimitRows), it doesn't work as expected. 

At first the report shows a total rowcount of 5. That's correct based on the default value that was set for the parameter. 

When I change the parameter in Power BI Service to 100, save this, and then refresh the report in Service, than it keeps returning only 5 rows.  I have tried both manual refresh of the report and a refresh by scheduled time. But it keeps using the initial value that I set for this parameter in Power BI DEsktop, which is 5. 

So it doesn't work as expected unfortunatelly. Is this a bug in Power BI Service, or do I miss something? 

Looking forward to your response!