Forum Discussion
Post PatronPower Query Preview (Auto-Sorting?!?!)
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.)
Post PatronHello edhans I agree with you that it "feels" like it has more to do with the database itself and/or the database driver. Not sure where that leaves me though as far as an action I can take.
However, when you select a table in the Navigator window where is there an option to apply any filter? Without that then doesn't it still have to generate a preview before you can apply a filter step? Problem is I can't get to that initial preview because it'll time-out/fail eventually.
Community ChampionIn 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.)
- robarivas
Hey edhans When I supply a written SQL statement the problem goes away. The preview is instant (more or less). I'm not sure I actually have access to any other tables but I'll see if I can find out and I'll also look into the possibility of getting a view set up. The table is roughly about 250 million rows and around 120 columns.
- edhans
Great robarivas - glad you are at least able to see some data. Can you mark one of these as the solution so this thread can be marked as solved - at least for this minor mystery?
Be sure to get a view, or add a filter in your SQL statement. Otherwise, when this loads, Power Query will read all 250,000,000 records before doing transformations as it will not be able to fold any other steps since you hardcoded the connection query.- robarivas
Thanks edhans I'll mark one as a solution as a favor to you for helping out and because I think this issue (if its really on my side) can't be addressed by the Community. But in actuality I feel this is very unresolved. Until I can be shown (like by a DBA on my side perhaps) that the database and/or its driver have some kind of configuration problem, I won't see this issue as resoved. Nowhere in any Power Query documentation, articles, books, etc. have I seen a requirement or recommendation that views and/or hand-written SQL be used. I'd say I've probably only ever seen the opposite recommendation.