Power Query Preview (Auto-Sorting?!?!)

Hello robarivas 

I never worked with Oracle, only SQL and I don't know if they behave differently. But I never saw Power Query sorting something automatically. Did you put the sorting in your code somehow? Or why do you know that Power Query is sorting the data?

BR

Jimmy

I believe what Power Query is doing in the data preview window is trying to return meaningful results. I know when you are doing transformations in SQL Server data it will do some ordering and get the top 4,096 rows as well. This is a sample of what Power Query does when accessing the AdventureWorks database in SQL and returning the results to the Power Query window while I am editing queries:

select top 4096
    [$Ordered].[CustomerKey],
    [$Ordered].[GeographyKey],
    [$Ordered].[CustomerAlternateKey],
    [$Ordered].[Title],
    [$Ordered].[FirstName]
from 
(
    select [_].[CustomerKey],
        [_].[GeographyKey],
        [_].[CustomerAlternateKey],
        [_].[Title],
        [_].[FirstName]
    from [dbo].[DimCustomer] as [_]
    where (([_].[GeographyKey] <> 7 or [_].[GeographyKey] is null) and ([_].[GeographyKey] <> 9 or [_].[GeographyKey] is null)) and ([_].[GeographyKey] <> 10 or [_].[GeographyKey] is null)
) as [$Ordered]
order by [$Ordered].[CustomerKey]

That is to attempt to provide you the most meaningful data when doing transformations. When the actual referesh is happening though, the code is much smaller and does no automatic ordering.

select [_].[CustomerKey],
    [_].[GeographyKey],
    [_].[CustomerAlternateKey],
    [_].[Title],
    [_].[FirstName]
from [dbo].[DimCustomer] as [_]
where (([_].[GeographyKey] <> 7 or [_].[GeographyKey] is null) and ([_].[GeographyKey] <> 9 or [_].[GeographyKey] is null)) and ([_].[GeographyKey] <> 10 or [_].[GeographyKey] is null)

I do not think there is a way to turn that off, and performance is certianly better than if it were to return all rows (1M, 100M?) in an unordered way during the Power Query editing process. 

Hi all,

We are also seeing the same behaviour with the Denodo Connector with Power BI. We have a Denodo Virtualisation over a SQL Server. When I connect to a large table via Denodo it wants to sort the preview / sample data with the primary key. When I go direct to the SQLServer I notice this is not the behaviour. So one solution for us was to remove the Primary Key meta data from Denodo and bang!! it works. So its Power BI behaviour with certain data sources... 

Have been looking for a solution everywhere ... nothing.

Let me know if someone has a solution to modify this behaviour in Power BI... crazy, several billion rows in a table in Direct Query mode and it wants to sort by the primary key before giving me the first 200 rows ????

Nick