Forum Discussion

awiefoong's avatar
awiefoong
Frequent Visitor
5 years ago
Solved

Not reading the last row in Excel sheet

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.

Need Help
  • awiefoong's avatar
    awiefoong
    5 years ago

    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.

8 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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.

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      awiefoong
      Frequent Visitor
      5 years ago

      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's avatar
    awiefoong
    Frequent Visitor
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        awiefoong
        Frequent Visitor
        5 years ago

        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.

  • MEGHS's avatar
    MEGHS
    Frequent Visitor
    4 years ago

    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!