Oct 09 2019 05:48 AM
Can anyone help me troubleshoot an issue with Power Query.
Everything was working fine and then, after a certain time, I could no longer pull through new or updated data from the source file.
The think the issue is with the source not destination file: there is no error showing in Excel or Power Query in the destination file; I have also tried rebuilding the query from scratch using a new file, but the the exact same data is pulled through, missing the latest rows and updates. (326 rows in source, 325 in destination; row 98 column M not updated in destination file).
What could be causing this?
Oct 09 2019 06:10 AM
Oct 09 2019 06:14 AM
I saw that suggestion on another thread: tried it, no change.
Oct 09 2019 06:27 AM
So, in destination file you have all rows but 98 from the source file and there is nothing that may cause error in row 98, correct?
Oct 09 2019 06:31 AM
Correct.
After the entry of one particular row, data subsequently added to the source file will not pull through, and the source file continues to work as normal.
I cannot see any error in that row or the source spreadsheet which may cause this issue.
Have tried rebuilding the destination file query from a new file, but I encounter the same error.
Oct 09 2019 06:45 AM
But you didn't add row 98, it somewhere in the middle. As I understood Power Query returns row 97 and all rows before that, as well as row 99 and all rows after it. Only this one is skipped.
Oct 09 2019 06:51 AM
Again, you are correct. There are two queries:
Sheet A Source --> Sheet A Destination - Data up to row 345 included. 346 onwards not pulled through.
Sheet B source --> Sheet B Destination - Data in Row 98 col M calculating correctly (formula in row 98 references row 346). Data is destination file sheet B shows results as though sheet A row 346 does not exist.
It's as though the ability to pull data from the file was lost after that row was added.
Oct 09 2019 07:15 AM
@neilingoe , not sure what it could be. Perhaps you may provide copy of your file removing all sensitive and extra information?
Oct 09 2019 07:27 AM
Unfortunately the whole file is sensitive data!
Are there any other diagnostics I could run to get to a possible cause?
- The source file data is still within the same table.
- I have tried removing and re-entering the data manually.
- I don't want to recreate the source data table as it's being fed by a lot of automations and I would have to redo those.
What would cause a query to break but not give an error message?
Oct 09 2019 07:59 AM
Unfortunately nothing concrete. Try to localise the issue - delete all other queries but one in question, don't load into data model if you do, remove top and bottom rows around one in question and like.
Oct 09 2019 08:28 AM
@Sergei Baklan it appears the answer is "a pending Windows update".
It's working again now.
Sorry I wasted your time.
Oct 09 2019 09:08 AM
@neilingoe , that's okay, thank you for sharing your finding