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.
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.
Hi Anonymous,
Formatting as a table is recommended though the worksheet is workable. Please refer to guided-learning/powerbiandexcel?tutorial-step=2.
Best Regards,
Hi Anonymous ,
Could you please mark the proper answers as solutions?
Best Regards,
