Forum Discussion
"Basic" SQL editor required for ODBC connection to DB2
In Office 365 I now have to use the Data tab to establish an ODBC connection to a DB2 for z/OS database, selecting "From other sources", "From ODBC" and then connecting to the DB2 database.
I can enter an SQL query under "Advanced options" and then Load the returned data into a worksheet.
Having done this, is there an "easy" way to edit the SQL query without having to try and use the horrendous Advanced Editor in Power Query Editor?
In older versions of Excel I used to be able to very quickly edit the query by right-clicking the data and selecting Table, External Data Properties and then Definition, or the Table, Edit Query option. I am despairing of ever using Excel again, as this new interface is horrible.
All I want is a simple text box where I can enter / edit the SQL just like I used to be able to 😞
Regards,
Steve
6 Replies
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
