Forum Discussion

Diamond Contributor

Feb 22, 2017

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

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

BI & Data Analytics

Get & Transform Data

Guy Hunkin

Microsoft

Feb 26, 2017

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

SergeiBaklan

Diamond Contributor

Feb 27, 2017

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.