Power Query: Xlsb vs Xlsx

Recently I helped my colleague with a report. The task was to consolidate data from multiple Excel files, each contains 100k-500k rows. Hence, size of each Excel file is relatively big.

I proposed to use xlsb to store those files. One file per month, report needs R24m, so 50 MB is less than 150 MB.

In the end I finished with deep analysis what is better as Excel-files database for Power Query – XLSB or XLSX.

Resume

For PQ solutions use XLSX instead of XLSB

PQ from XLSX twice faster than from XLSB

Engine handling XLSB from time to time return error – cannot read file

“UseHeaders” parameter of Excel.Workbook function works differently for xlsx and xlsb

If sheet and table names are equal in Excel workbook – Navigator generates unique name of object.

If you interested how I revealed all this – continue reading.

Files for tests

I thought that PQ works faster with smaller xlsb files, but I was wrong.

In the same time I knew that some people had problems with xlsb, e.g. issue.

Nevertheless, I started with xlsx with idea in mind to switch to xlsb later.

First surprise for me was that if sheet and table on this sheet have same name – PQ engine renames table

Don’t worry, despite in Navigator we see DATA1, M-code access table object with name DATA.

let

Source = Excel.Workbook(File.Contents(“C:\Temp\PQ Samples\2015 09.xlsx”), null, true),

DATA_Table = Source{[Item=”DATA”,Kind=”Table”]}[Data],

#”Changed Type” = Table.TransformColumnTypes(DATA_Table,{{” Lcurr.”, type text}})

in

#”Changed Type”

Then I saved xlsx file as xlsb

And tried to apply same code

let

Source = Excel.Workbook(File.Contents(“C:\Temp\PQ Samples\2015 09.xlsb”), null, true),

DATA_Table = Source{[Item=”DATA”,Kind=”Table”]}[Data],

#”Changed Type” = Table.TransformColumnTypes(DATA_Table,{{” Lcurr.”, type text}})

in

#”Changed Type”

Got an error and analyzed query step-by-step.

First step – get Excel file structure – similar formula – different result

Structure is different. We see “DATA” object for xlsb, but it is not clear – is it a table of sheet.

I renamed sheet and checked this step once again

OK – column Name contains sheet names.

Second step – Navigation – didn’t work for xlsb

Error, as expected, because first step for xlsb doesn’t contain Kind and Item columns.

Not a big problem, just need to fix navigation step.

Nevertheless, this step that doesn’t allow simply switch from xlsx to xlsb.

One click on Table in Data column and we see result:

Now you probably want to apply Table.PromoteHeaders function, but wait, read description of Excel.Workbook function

Second parameter – useHeaders, will help to avoid additional transformation step. Turn it on and query XLSB

Wow, this was not expected.

Be careful with this parameter – some characters will be replaced by # if you work with xlsb.

Dot – definitely, rest special characters – I didn’t test.

Let’s save solution with PromotedHeaders and check speed.

Transform query into function

Let

fGetXlsb = (path as text) =>

let

Source = Excel.Workbook(File.Contents( path ), null, true),

sheet = Source{[Name=”DATA”]}[Data],

#”Promoted Headers” = Table.PromoteHeaders(sheet)

in

#”Promoted Headers”

in

fGetXlsb

Do the same for XLSX and load data to Data Model

Simple VBA code that check Power Query refresh with data load time

Sub test_time()

Dim start as Single

start = Timer

ThisWorkbook.Connections(“Query – QueryXLSB”).Refresh

Debug.Print “xlsb: “; Timer – start

start = Timer

ThisWorkbook.Connections(“Query – QueryXLSX”).Refresh

Debug.Print “xlsx: “; Timer – start

End Sub

What I also noticed – cosmetic difference in PQ editor

I saw somewhere – “DAX is easy but not simple”, same is relevant to Power Query.