Power Query Ideas on Uservoice and Power BI Communities

Recently I posted about UsedRange trap when importing from Excel sheet in Power Query (you can read this post there).

To add an option for users who can meet the same problems as me I’ve added corresponding Power Query improvement ideas on excel.uservoice.com and https://ideas.powerbi.com

Please add your votes to these improvements – I’m sure they can not only save you a few rows of code but also can save you from potential pitfalls when working with raw Excel data.

Thank you!


Excel sheet as a source to Power Query and Power BI: a pitfall of UsedRange

When you import data from an Excel workbook to the Power Query or Power BI from entire sheet, be careful, there is a pitfall.

After linking to an external Excel file there are three options of data extracting available:

  1. From table (table-formatted range of cells in the sheet),
  2. From custom named range of cells,
  3. From entire sheet

In the first case, a Table object is already structured data with columns’ names, automatically transformed to PQ tables. In the second case, Power Query shall give the named range generic titles (Column1, Column2, etc.) and then work as before.

However, it is often the case that data are not structured in a formatted table or named range, and it can be difficult to transform them to such view before import. There can be many reasons for that, e.g., cells format is to be saved (merged cells are no longer merged after transformation) or there are too many files to transform them manually.

Data on a “raw” sheet

Fortunately, Power Query can extract data from the whole sheet. To get data from unformatted sheet you do not need to perform any special actions: just connect to the file, find the needed sheet (it will have “Sheet” value in the [Kind] column) and get data by retrieving its content from the [Data] column:

Excel sheets available as data sources just as tables or named ranges

The question is: what data range will be retrieved in that case? There are 17 179 869 184 cells on an Excel sheet (16 384 columns and 1 048 576 rows). If Power Query try to get them all, there will be huge memory consumption and performance leak. However, we can ensure that usually number of imported rows and columns is about the same as the number of rows and columns with the data may be slightly bigger.

So how Power Query defines a data range on a sheet? The answer is out there if you familiar with VBA macros and have enough experience with an Excel object model (but I think you will not be glad with this answer).

Continue Reading