Third parameter for Excel.Wokbook() function - what's that?

[Sergei Baklan]

This function always had two parameters as it is documented for today

Excel.Workbook(workbook as binary, optional useHeaders as nullable logical) as table

https://msdn.microsoft.com/en-us/library/mt260859.aspx

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...

[#2]

Hi Sergei!

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.

Guy

- Excel Team

[#3]

Hi Guy,

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.