Access Excel Data Model (Power Query) tables from ODBC

2
votes

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.

1
excelpowerquery
Power Query doesn't store tables. It connects to data sources and transforms them into tables that you load into your data model and/or your worksheet(s), so your SQL code would just be connecting to a spreadsheet. – Alexis Olson
Sorry, your comment does not help me much further. Reading from worksheets is no problem (this is well documented). Reading from tables in the data model is a mystery (may be not supported). – Erwin Kalvelagen
That does not answer your question, but, why are you trying to pass through Excel? You probably have a reason for that, but I don't see any reasons that would force you to use Excel. Why not use your external application to connect to the same data your Excel does? Instead of doing Data-->Excel-->Application simply do Data-->Application... – CharlesPL
Using your external application, have you tried to look at the schema to get the table names of your Excel file? If you don't see any tables there, I won't put much hope on this approach. – CharlesPL
@CharlesPL Well, my idea was that this would make it easier to share data sets (just e-mail a spreadsheet file; the tables in the data model are highly compressed). – Erwin Kalvelagen

1 Answers

2
votes

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.