Not reading the last row in Excel sheet

[awiefoong]

Hi all,

I am having a pretty weird problem: I have monthly data files (around 50 to 60,000 rows) which are saved in a folder and refreshed on a monthly basis. I noticed that Power Query always read all but the last row of data in the Excel sheet. I have dealt with all errors but the problem still persists. The implication is I will lose the last row of data in my Excel sheet. I saw a similar thread from 2017 that has remained unsolved. Wonder if any of you have encoutered similar problem and have a solution to it? Many thanks in advanced.

[MEGHS]

found this thread as I'm facing the same problem, has there been any better solution than converting it to a table? I would like to avoid this additional step in my data prep. Thanks in advance! 

[edhans]

Glad I was able to help. There are ways to do it without being an Excel table, but we'd need to see actual data/files to really test and see why those final records were not being included.

[edhans]

@awiefoong - question - is this in an Excel Sheet and just a grid of data or is this in an Excel table? If the latter, is it possible that the last record was inserted below the table and the table didn't auto-expand to include it?

[awiefoong · Accepted solution]

Thanks @edhans I converted the Excel sheet to Table and the problem is solved. Unfortunately it is not the ideal solution as this means there are a couple more steps needed to the exported file.

[awiefoong]

@Jimmy801 Many thanks for the suggestion. There isn't any filter and I still have no idea what's wrong with the original Excel sheet... But you are right that the data file was exported from system. Hope to hear from someone who has faced similar issue and have solved the problem 😉

[Anonymous]

Hi @awiefoong ,

Try copying data in the new (xlsx or csv) file and then get data from this (new) file. Does the error persist?

As mentioned by @Jimmy801 this is most likely an issue with the file itself, not PBI or connector issue. Can you export it in a different format (e.g. csv vs. xls)? If yes, try this too.

Kind regards,

JB 

[awiefoong]

Thanks @Anonymous. Yes it is an issue with the Excel file. I convert the Excel sheet to Table and the problem is solved. Unfortunately it is not the ideal solution as this means there are a couple more steps needed to the exported file.

[Jimmy801]

Hello @awiefoong 

first check if you somewhere have a function or a filtering step in your query. Could be also something like Table.SelectRows(FirstStep, each true).

If this is not the case then there is probably a problem with your Excel-file. Maybe is a corrupt file created as a export from a third-party software or a xls-file or somehow the reading-range of the Excel-file is wrong.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy