CODEWITHSUNDEEP
excelpowerquery
Erwin Kalvelagen
Erwin Kalvelagen

Reputation: 16724

Access Excel Data Model (Power Query) tables from ODBC

We can access Excel data using ODBC (Excel ODBC driver). Can we also access the data in the data model (i.e. Power Query tables)? Basically I am thinking about (mis)using Excel/Power Query as a database and let an external application retrieve data from it (using SQL).

To read from Sheet1 I can do: 

SELECT ... FROM [Sheet1$]

but

SELECT ... FROM [table in data model]

does not seem to work for me. Is this supposed to work or is this not supported at all?

There is a ton of information about Power Query using ODBC to import data. Here I am looking at the other way around.

Upvotes: 2

Views: 1895

Answers (1)

Andrey Minakov
Andrey Minakov

Reputation: 566

You should distinguish for yourself Power Query tables and Data Model (Power Pivot) tables. You can set up some PQ tables as tables, loadable to DM, so data will be "transferred" from PQ to DM only for that particular tables.

I'm pretty sure that it is impossible to get data from "PQ only" tables. You can just get m queries (not their results) via VBA or unpacking Excel.

Regarding PP (DM) tables. Actually, there is Analytical Services (VertiPac) engine inside Excel (just in case - as well inside PowerBI Desktop). So as soon as you start Excel or PBI, you actually start AS engine instance as well. The data in it are reachable via:

  1. Excel VBA (Visual Basic for Applications). You have Thisworkbook.Model.DataModelConnection.* API, and can get to data itself and to model as well. This is the only "official" way to get the data programmatically.

  2. Power Query - as Analytical Services data source. This is unofficial way, but I read, that Microsoft told that they are not going to close it in the future (but you never know :-)). E.g. Dax Studio can do that - https://www.sqlbi.com/tools/dax-studio/. Unfortunatelly, while getting to PBI AS service is quite easy, I don't know how to get to Excel AS service without Dax Studio. As far as I understand, the main problem here is how to get an AS port number, launched by Excel. But I hope that this info will at least help you understand the way for further searching, if you want to go Power Query way. Or may be it is reasonable to use Power BI Desktop for the task.

  3. Excel is just a zip file, so definitely AS files are inside of it. I never went this way, but you can observe what is inside exel zip - possibly the AS files may be in some useful form there.

Upvotes: 2

Related Questions