Forum Discussion

robarivas's avatar
robarivas
Icon for Post Patron rankPost Patron
5 years ago
Solved

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

I'm connecting (thru Power Query in Power BI) to a very large table in Oracle. The preview is not generating (fails and/or times out). Turns out its because Power Query (as part of the Navigation ste...
Need Help
  • edhans's avatar
    edhans
    5 years ago

    In the initial connection screen robarivas you can write your own SQL statement. I activily recommend against this in production as it will break 100% of all future query folding. But in this scenario, it can assist in trouble shooting.

     

    Does it do this with any table on that server, or just this one? if it just this one, it might be an insanely massive table and the driver isn't coping with it well. If that is the case, consider working with your DBA to privide a view to you to use vs the table. Then connect to the view. All transformation later that Power Query can convert to SQL statements will fold.

     

    If it is all tables though, then there is definitely a problem with the server configuration or your driver (ensure you have the latest Oracle driver for Power BI.)

     

     

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
5 years ago

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

robarivas's avatar
robarivas
Icon for Post Patron rankPost Patron
5 years ago

Thanks Jimmy801

I did not do any sorting in Power Query. Here is the M code in my query:

 

let
Source = Oracle.Database("Oracle", [HierarchicalNavigation=true]),
SCHEMAX = Source{[Schema="SCHEMAX"]}[Data],
TABLENAME = SCHEMAX{[Name="TABLENAME"]}[Data]
in
TABLENAME
 
I know its sorting because the Oracle DBA sent me the SQL being run in the database when the M code above executes. Here is the SQL code:
 
SELECT "FIELD1",
"FIELD2"
FROM ( SELECT "$Ordered"."FIELD1",
"$Ordered"."FIELD2",
ROW_NUMBER () OVER (ORDER BY "$Ordered"."FIELD1")
AS "$Row"
FROM "SCHEMAX"."TABLENAME" "$Ordered"
ORDER BY "$Ordered"."FIELD1") "$Paged"
WHERE "$Paged"."$Row" <= 4096
ORDER BY "$Paged"."$Row