Maxim Zelensky on 23 May 2017 23:21:11
When importing data from non-formatted Excel worksheets (not from tables or defined names) Power Query uses an UsedRange property of worksheet (as described there https://social.technet.microsoft.com/Forums/en-US/9d38f271-2cc6-4170-9a48-1e4f45f6f125/missed-column-from-xlsx-worksheet-on-import?forum=powerquery and there http://excel-inside.pro/blog/2017/05/23/excel-sheet-as-a-source-to-power-query-and-power-bi-a-pitfall-of-usedrange/). This behavior cause problems when there are first empty rows and/or columns on the sheet, which may or may not be imported depending on formats. It creates problems when working with a lot of files in folders.
As Power Query dont work with formats, there is no reason to rely on UsedRange.
Please add an option to
-- select top left cell of the cells range to be imported,
or
-- select range address to be imported,
or
-- start import from the A1 cell
- Comments (1)
RE: Allow to select custom data range on Excel sheet on import
Honestly this *has* to go in the next release.
The behavior of silently dropping empty columns before the data produced massive data inconsistencies in an actual work project.
If the options are too hard to implement then the import of excel worksheets should always start from A1 to ensure consistency.