Feb 22 2017
- last edited on
Jul 12 2019
This function always had two parameters as it is documented for today
Excel.Workbook(workbook as binary, optional useHeaders as nullable logical) as table
However, with the latest PQ update connector to Excel file generates 3 parameters, it looks like
Source = Excel.Workbook(File.Contents("TheBook.xlsx"), null, true)
Second parameter (here is null) works as expected. What is the third parameter for i was not able to recognize, it affects nothing in my short tests.
Perhaps it's somehow connected with modified Combined Binaries introduced in Jan/17 update. It auto-generates "Transform ..." queries which are based on Excel.Wokbook(). That's only the guess.
So far have no idea what to do with third parameter. Except to remove it at all - otherwise people who have no recent G&T updates (more exactly, Excel 2016 updates - majority of them even don't know what G&T exists within Excel) receive an error what some functuion receives 3 parameters instead of 1 or 2...
Feb 26 2017 09:47 AM
Originally, Excel.Workbook used to read the entire workbook data to accuratly assign types to the columns. This was EXTREMELY slow on large workbooks.
Having this in mind, we added this flag to delay the behavior. When set to “true”, we don’t infer any column types. Instead, the UI uses its normal inference algorithm to generate a Table.TransformColumnTypes step with the inferred types and if it inferred wrongly, the user can update the transformation explicitly.
- Excel Team
Feb 27 2017 02:27 AM
Thank you, that's clear now.
I tried to apply timing to M script which loads Excel wookbook (not very large, ~5K rows x 17 columns), it gave about 10% in performance with changing 3rd parameter. Perhaps in some othere scenarios that will be much more effective.
For me even more important the engine don't infer column types. Less guesses M script does the better - but that's my own approach to work with it.