Forum Discussion
"Basic" SQL editor required for ODBC connection to DB2
Power Query Advanced Editor is designed for M-script, not for SQL query, and works quite good with it. If you skip adding SQL query at the first step and just connect to database, you may select tables, views, fields, etc in Power Query Editor without using Advanced Editor, keeping the latest for more complex cases - but that will be M-script.
Hi Sergei,
My DB2 source tables contain (literally) billions of rows of performance data and so I need to use the SQL to perform filtering and return just the data I want, especially if I want to join multiple tables. I then want to be able to re-use the query so that I can change things easily e.g. time periods. I used to be able to do this in older versions of Excel, but can't in Office 365.
If I enter the SQL when I establish the connection, the data gets brought back in a "preview" mode and then the query gets run a second time when I chose the option to Load the data - seems a bit of an overhead to do things in this way.
If I skip adding the SQL query, I get presented with a list of tables / views and then don't seem to be able to enter an SQL query to perform the filtering I need.
Power Query supports folding mechanism. For example, you query the table with few dozens millions of rows, on second step remove unused columns, on next step filter what needed. If use correctly PQ generates SQL query in background (it could be seen from right click menu on steps). Thus it doesn't load millions of rows on first step and perform other operations one by one on them, It loads final result.
Relationships in many cases could be recognized and related records are returned.
If course, if you do not rely on above mechanisms you may use native SQL query, with that folding won't work. One or another. And again, if old query interface was designed to generate SQL query, Power Query use another mechanisms.
However, in File-Options->Data you may activate legacy interfaces (they are disabled by default)
After activation you may use them from this menu
SergeiBaklan
Hi Sergei,I think there's probably something wrong with my setup, as I can't use the legacy Data Connection Wizard option in 365. If I select this and chose my DB2 database, Excel just disappears - no error messages on the screen at all. I don't know why / how this is different from establishing an ODBC connection from Other Sources. I'll see about raising a fault with our internal support team.
We've recently migrated from Win 7 to Win 10 and I've had to update my IBM ODBC drivers to a new version as well.
Some of my source tables have about 400 columns and I might only want 20 columns in my data, so it's much easier if I can use an existing SQL query rather than having to remove 380 columns 🙂
Thanks for your help