There might be occasions where you want to import data from Excel into Power Query or Power BI using cell coordinates like a range from E3 until G9 for example (“A1 cell reference style”). The function I provide below also caters for the potential pitfalls of this task that Maxim Zelensky has described in his article.
If your worksheet has one leading empty row and column, the import will ignore them and automatically return the range starting from B2. So to fetch the range E3:G9 you have to delete the first row and the first 3 columns. But as Maxim has found out, remaining formats on empty cells will lead to an import of empty rows and columns. So the number of rows and columns to delete will vary and is hard/impossible to predict.
The range that PowerQuery or PowerBI will import is stored in the Excel-file already in the sheet-data and the xml looks like this (“Sample3” from Maxims data):
The imported range is E1 till J12, as the first rows contain formatting instructions, and will therefore be imported as well. In the 3rd row E3 shows up with the first value, which is surrounded by “<v>”.
This is how it looks like in the Xml.Table in the query editor:
Task is to calculate the number of rows and columns delete, considering the individual offset that is caused by the formatted empty cells.
So I’ve cooked together these ingredients in a pretty massive code that you can download here: fnImportFromExcelCellCoordinates.txt
How to use the function
After you’ve copied and pasted the code into a new query in the advanced editor, you can invoke the function and have to fill in the following parameters:
Or watch the short screencast to see it in action:
You cannot import a range starting in a cell that’s not within the imported range: This code will only delete leading rows and columns. So the code will currently fail if the entered coordinates are without this range. Please leave a comment if you need that & I will adjust the code.
- Unzip your Excel-file with M by Mark White
- Explore your Excel file-structure in Windows Explorer by Professor Excel
Enjoy & stay queryious 😉