Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Excel DataSource doesn't load completely

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? 

Tips and Tricks

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
    BA_Pete
    Super User
    4 years ago

    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