How to check the query of an existing odbc excel how to#
How to check the query of an existing odbc excel code#
How to check the query of an existing odbc excel professional#

How to check the query of an existing odbc excel professional#

I did this using Microsoft Office Professional 2013.

How to check the query of an existing odbc excel how to#

This is how to dynamically connect to and query a MySQL database table directly from MS Excel on a localВ development machine. How large? I don't know but I do know some people can not load large sets of data to their worksheet.After swatting up on connecting QlikView to a MySQL database, I used the knowledge gained to create a similar set-up, this time with Microsoft Excel. Hope this solution helps some people, though keep in mind, for very very large datasets, this may not be a solution, as you won't be able to load all that data you want cached into a workbook if its too large. It's simply too complicated for the business user to delve into all this, esp when its a tool made for business users. I suggest Microsoft build a workflow and refresh schedule into Power Query for Excel (and Power BI etc), and allow freezing of base queries or something. This made it super fast, without all the mumbo jumbo. I then change my reference queries to source their data from this new query. I then create an Excel named reference, (select say columns at top) and name it, then Get Data from Range (this is essentially the cache). I manually cache it by loaded to the worksheet. ?ДЄnyhow the intention of this is to prevent multiple calls to the base query (sql query + with some transforms), by caching it.

I can not find in Excel, the option to turn off parallel load option, this seems to be a tip but seems to only exist in Power BI Desktop. I have this problem with multiple calls to the main sql query from reference queries. This entry was posted in Excel, Excel Add-ins, Office 2013, Power Query, PowerBI by Ken Puls.

How to check the query of an existing odbc excel code#

So if I have some funky M code I need to re-use, it makes it way easier to review it and copy it. Why is that important? Because I can drill through the other queries and get at their M code without having to close the window and go back to Excel first. The other thing I like about this method is that it immediately gives me access to that queries pane. We now have a new query in the editor that we can edit, without loading it into a worksheet first.

Right click the Base Connection query and choose вЂњReferenceвЂќ.

When you expand that, you get a list of all Power Queries in the workbook. On the left side we can see a collapsed вЂњQueriesвЂќ pane. Now weвЂ™re taken into the Power Query window.

Right click the Base Connection query and Edit.

Instead of following the method above, this time we will: I included it in my last post, but I thought this was worth calling out on its own. I learned a new method last week from one of the Power Query team members which is much better (thanks Miguel!). Reference other Power Query queries - The New Way I have to wait for that to finish before I can edit the new query and customize it the way I want. Instead, it immediately loads it into a worksheet. The problem was thisвЂ¦ my intention was to reference and customize my query.

Right click the Base Connection query and choose Reference.

Until recently, I would create my Base Connection, then IвЂ™d do the following to create the new query to reference that one. Reference other Power Query queries - The Old Way I need to be able to reference other Power Query queries (namely my Base Connection) so that I could prune/trim/re-shape the data.

Next IвЂ™ll create as many queries as I need to re-shape the data in the Base Connection into the forms I need, then load those into the data model.

I then set it to only create a connection. My methodology generally goes something like this: (IвЂ™d rather have easy to understand relationships and simple DAX measures!) Methodology I can leverage this new tool to optimize my tables and build Power Pivot solutions that require less tricky and funky DAX measures to compensate for less than ideal data structure.

Why would I go to the effort of feeding through Power Query first? IвЂ™m no SQL ninja, and I find Power Query allows me to easily re-shape data in ways that would be hard with my SQL knowledge. But in order to build things the way I want, I need an easy way to reference other power query queries. In order to accomplish that goal, IвЂ™ve begun using Power Query to source data over Power PivotвЂ™s built in methods.

One of the things I really like to do with Power Query is shape data into optimized tables.