Power Query - connection troubleshooting

Copper Contributor

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?

11 Replies

@neilingoe 

Did you try to clear the cache?

image.png

 

 

@Sergei Baklan 

I saw that suggestion on another thread: tried it, no change.

@neilingoe 

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?

@Sergei Baklan 

 

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.

@neilingoe 

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. 

@Sergei Baklan 

 

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.

@neilingoe , not sure what it could be. Perhaps you may provide copy of your file removing all sensitive and extra information?

@Sergei Baklan 

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?

 

@neilingoe 

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.

@Sergei Baklan it appears the answer is "a pending Windows update".

 

It's working again now.

 

Sorry I wasted your time.

@neilingoe , that's okay, thank you for sharing your finding