Importing MySQL query into Microsoft Excel using Power Query

Question

I'm trying to import a MySQL query into MS Excel using Power Query. I get a database connection error, although everything else is working fine (the query, in a different program, included). I assume this would be due to the fact that the code contains user-defined variables, as such:

    SET @c:=1

Apparently Power Query doesn't like that, because replacing all instances of this variable makes the code work.

I could replace this variable by its value, but that would cost me a lot of time and would not be very elegant. I found what seemed to be a solution here, but if I try to do something along the lines of that, I get asked for permissions I cannot give, as this might affect the database.

Does anyone have an idea about what I need to do? Any workarounds? Any help would be greatly appreciated. Thank you!

Answer 1

My general approach would be to rebuild the logic of the SQL script in Steps using the PQ UI. E.g. the first FROM table becomes the first table/view you navigate to, JOIN becomes Merge Queries, WHERE clause becomes Filter steps, UNION becomes Append Queries, etc.

I've built a lot of amazingly complex Queries this way - one step at a time. It is a very flexible tool and a useful skill once you get going. After over 20 years of coding in SQL, I now use PQ as my SQL query tool of preference. It has so many advantages over custom code and generally it does a good job of "folding" your steps into a generated SQL statement.

Power Query has it's own Parameters functionality - perhaps you can use that to replace the variables. It depends on how your variables are being used.