Power Query & ODBC

[Judit950]

Hi Everyone, 

I hope you are all well! :)

I am new to Power Query and I have been trying to load data from an Excel workbook with several tabs / calculations into Power Query via the Data ribbon > Get Data > From File > From Excel Workbook.  I then select the excel sheets I wish to transform.  After transforming the data, I find launching the Power Query editor is very slow.

A such, I wanted to try to use ODBC instead to see if this would be any faster.  However, upon selecting the workbook for import under Data ribbon > Get Data > From Other Sources > From ODBC (and selecting the Data Source Name), only tables (in my case the named ranges I created in the source file) were visible for selection and further transformation.  The excel worksheets themselves were not visible.  As such, can someone tell me 1) Is it possible to connect to excel binary files effectively 2) Is it not possible to transform Excel worksheets in ODBC? 3) If not, how can I make the first method I described above any faster?

Many thanks for your help in advance, very much appreciated!

Judith

[#2 · Accepted solution]

@Judit950 

Power Query works with Excel binary files extremely slow and in background uses totally different (and limited) connector compare to ones for .xlsx files. Even if that's the same entry point From File->From Excel workbook.

You may try From Database->From MS Access database, select binary file and Transform - result will be the same since same mechanism uses both for binary files and MS Access database.

I don't think ODBC helps, however didn't test myself. Better to save file as .xlsx before query it - if that's possible.

[#3]

really?!? Power Query is slower on binary files? I thought 1 of the advantages of binary files was to improve performance. Learning something new everyday.

[#4]

@Sergei Baklan Many thanks for the information!  I tried to connect to the binary file via MS Access Database and this seems to have helped so thanks ever so much for the suggestion! :)