"Basic" SQL editor required for ODBC connection to DB2

[SJLamb]

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

[#2]

@SJLamb 

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.

[#3]

@Sergei Baklan 

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.

[#4]

@SJLamb 

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

[#5]

@Sergei Baklan 

One screenshot disappeared from previous post:

[#6]

@Sergei Baklan 
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

[#7]

@SJLamb 

Sorry, I had no experience with DB2, have no idea what could be an issue with the driver.

Of course, if you are more comfortable with SQL query, use it. I also use native queries from time to time, it depends on concrete project. The only my point PQ editor is not designed to work with SQL from user interface.

Just in case, for your sample it's not necessary to remove 380 columns, it's enough to select 20. That's easy enough from user interface only