How to import from Excel with cell coordinates in Power Query and Power BI

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.

Background

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.

Method

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.

Code

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:

Limitations

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.

Useful links

Enjoy & stay queryious 😉

Comments (10) Write a comment

  1. Pingback: #Excel Super Links #132 – shared by David Hager | Excel For You

  2. Hi Imke! Very nice code inside, with cool insights for me.
    I’d suggest to use a ‘dimension’ parameter from sheetN.xml, then you can get top-left (also as bottom-right) cell coordinates, compare it to the function arguments and cut (or add) rows/columns from Excel.Workbook [Data] table. So you do not need to dig into ‘rows’
    What do you think?

    Reply

    • Yes Maxim, you’re right: The code could have been a bit simpler.
      It contains the logic to automatically import without leading “empty” rows & columns, but during programming I decided to change the scope to import with coordinates. At that stage I should have simplified the logic, but forgot about it.
      Good spot!

      Reply

      • With an optional StartCell (if blank FirstNonEmptyCell) digging into rows is not redundant anymore. Text.Upper(Coordinates) might be reasonable.

        Reply

  3. @Imke – It is best to ask Excel to derive the Row-Column Co-ordinates
    Then you can do this with far less code
    Have a look at the file

    https://1drv.ms/f/s!AiKBTsYfZw-vgo1nVDYnx8rGRvPxHA

    or
    ———————————————————————————————————
    Define a Query : Data
    let
    Source = Excel.Workbook(File.Contents(fGetCellVal(“fPath”)), null, true),
    mData = Source{[Item=fGetCellVal(“fTblNm”),Kind=”Sheet”]}[Data],
    mSelRows = Table.Range(mData,fGetCellVal(“StartRow”)-1,fGetCellVal(“EndRow”)-fGetCellVal(“StartRow”)+1),
    mSelCols = Table.SelectColumns(mSelRows,Cols)
    in
    mSelCols
    —————————————————————————————————
    Define a function fGetCellVal
    let
    fGetCellVal=(tbl, optional row as number, optional col as text)=>
    let
    NoOfRows = try Table.RowCount(tbl) otherwise -1,
    Source = if NoOfRows = -1 then Excel.CurrentWorkbook(){[Name=tbl]}[Content] else tbl,
    rNo = if row = null then 0 else row,
    col = if col = null then Record.FieldNames(Source{0}){0} else col,
    Value = Record.Field(Source{rNo},col)
    in
    Value
    in
    fGetCellVal
    —————————————————————————————————————–
    Define a List Query : Cols
    let
    Source = List.Transform({fGetCellVal(“StartCol”)..fGetCellVal(“EndCol”)}, each “Column”&Text.From(_))
    in
    Source
    ——————————————————————————————————————–

    Reply

    • Hi Sam,
      thanks for the code. On my machine, it threw Errors: “Operation is not valid due to the current state of the object”. After some digging I found out that this was due to the string “col”. After I’ve replaced it with “cole” the code worked. I suspect that “col” is kind of a system-reserved string that can cause errors like this on machines with different locales. (Experienced sth similar with “document”)

      But it doesn’t return the desired result. Try switching to “Sheet3” of your sample data: It says that “Column5” wasn’t found. That’s because the import will ignore the leading empty rows and columns. You might want to read Maxim’s article that I have referenced above to get some background information about this behaviour. There you will also find the reason why I’ve chosen so address the “Range” from the metadata.

      BTW: Nice find with the syntax-sugar of the named ranges. Is there any documentation about this?
      Cheers, Imke

      Reply

  4. @Imke – Could never have guessed that Cols is a reserved name in some locales – do you know if MS has published a list of reserved names somewhere

    Also took care of the problem on Sheet3 – with the below modification but I realize that my code would run in to issues in other scenarios – like the ones described on sheet4 and sheet5. (have updated the files on the onedrive)

    You code is more robust and I now appreciate its complexity

    let
    Source = Excel.Workbook(File.Contents(fGetCellVal(“fPath”)), null, null),
    mData = Source{[Name=fGetCellVal(“fTblNm”)]}[Data],
    mRngData = if Table.RowCount(mData) = fGetCellVal(“EndRow”)-fGetCellVal(“StartRow”)+1 and Table.ColumnCount(mData) = fGetCellVal(“EndCol”)-fGetCellVal(“StartCol”)+1 then mData else Table.SelectColumns(Table.Range(mData,fGetCellVal(“StartRow”)-1,fGetCellVal(“EndRow”)-fGetCellVal(“StartRow”)+1),ColsReq)
    in
    mRngData

    Sam

    Reply

  5. Hello, first of all, thank you for this amazing function!

    I am trying to merge all the .xlsx files in a folder, and before applying your function what I have is a column with the file name, another with the folder path, another with the sheet name (there are many sheets to consolidate in each file) and a Data column with all the data of each file.

    I am trying to use your function, taking the FullFilePath from my “folder path” column, the SheetName from my “File Name” column and the introducing the cells by hand but it gives me an error, what am I doing wrong?

    Thank you in advance!

    Regards
    Rodrigo

    Reply

Leave a Reply