Forum Discussion
Get Data from Excel row 1 from sheet does not load
Well, further testing revealed the following. Blank rows at the top of the worksheet were ignored by Get Data - If 1. There was no value (null), 2. a cell on that row did not contain a different fomat from all other cells in that column.
Wow! this is nuts - the XML code "behind the curtain" defines a <dimension ref> tag that Power BI must read. It is not the whole "sheet" - as I thought, but some defintion defined by "Used" cells... I guess this makes some sense, or every row would load...
If you have a cell with a different format the row will be included - if not, it will not load - this was the case I needed to solve for - now I guess I will have to define/enforce another standard in our workbook.
Another option here would be to highlight your table of data and use the "Format As Table" option. Then you can also give this table a meaningful name and address it directly by name in your query. This will avoid the need to connect to $Sheet1 and skip x amount of rows. Tables will also auto-expand as new rows are added and they should be largely unaffected by data entered into other cells on the same sheet.