Excel DataSource doesn't load completely

[Anonymous]

Hi,

Power BI beginner here. 

I am creating a dashboard with multiple excel sources . The excel file currently has 300k+ rows !! (I recommended not to use excel for storing data but the end user needs to validate data in excel and to store in a different location)

I tried loading this file into Power Query and only 173k+ rows are loading. I don't see any errors in the transformation steps either. Any reason why this could be happening? 

[Anonymous]

I checked on the file and no blank rows in between and no defined table / range as well. It is actually an output file from Alteryx where new rows of data are appended to the existing sheet. Also I tried automated workflow to save it as csv file and there were no issues. Solved my problem for now, but i am still curious to understand as to why there is problem in loading the file in .xlsx format

[BA_Pete]

Hi @Anonymous ,

I would check the following:

- Ensure there are no completely blank rows in the Excel dataset. A break in contiguous data will prevent Power Query from seeing it as a single set of data.

- If your data is formatted as a 'proper' table, ensure that the bounds of the table reach right to the end of your dataset.

- If it's not formatted as a 'proper' table, then format it as such. Then try importing to Power Query using the named table reference, rather than the sheet name.

Pete