Forum Discussion
Get Data from Excel row 1 from sheet does not load
Reading Excel Sheet, row 1 is blank, headings are on row 4, so I need row 1 to be there so I can perform a step to remove tops rows. On some worksheets the blank row 1 is not even recognized - it is there, it is blank, but Get Data does not get row 1.
Other workbooks with blank row 1 load correctly - Get Data sees the row and I can process the remove top rows step and have a good data table.
What could be causing this?
4 Replies
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.
- d_gosbell
Super User
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.
v-jiascu-msftMicrosoft Employee
Hi Anonymous,
Formatting as a table is recommended though the worksheet is workable. Please refer to guided-learning/powerbiandexcel?tutorial-step=2.
Best Regards,
- v-jiascu-msft
Hi Anonymous ,
Could you please mark the proper answers as solutions?
Best Regards,
