Home

Power Query - connection troubleshooting

%3CLINGO-SUB%20id%3D%22lingo-sub-902109%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20connection%20troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902109%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anyone%20help%20me%20troubleshoot%20an%20issue%20with%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEverything%20was%20working%20fine%20and%20then%2C%20after%20a%20certain%20time%2C%20I%20could%20no%20longer%20pull%20through%20new%20or%20updated%20data%20from%20the%20source%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20think%20the%20issue%20is%20with%20the%20source%20not%20destination%20file%3A%26nbsp%3B%20there%20is%20no%20error%20showing%20in%20Excel%20or%20Power%20Query%20in%20the%20destination%20file%3B%20I%20have%20also%20tried%20rebuilding%20the%20query%20from%20scratch%20using%20a%20new%20file%2C%20but%20the%20the%20exact%20same%20data%20is%20pulled%20through%2C%20missing%20the%20latest%20rows%20and%20updates.%20(326%20rows%20in%20source%2C%20325%20in%20destination%3B%20row%2098%20column%20M%20not%20updated%20in%20destination%20file).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20could%20be%20causing%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-902109%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902199%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20connection%20troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902199%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422885%22%20target%3D%22_blank%22%3E%40neilingoe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDid%20you%20try%20to%20clear%20the%20cache%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20501px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F136076i7C2557B6650286C5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902209%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20connection%20troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902209%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20saw%20that%20suggestion%20on%20another%20thread%3A%20tried%20it%2C%20no%20change.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902298%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20connection%20troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422885%22%20target%3D%22_blank%22%3E%40neilingoe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20in%20destination%20file%20you%20have%20all%20rows%20but%2098%20from%20the%20source%20file%20and%20there%20is%20nothing%20that%20may%20cause%20error%20in%20row%2098%2C%20correct%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902324%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20connection%20troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902324%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECorrect.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20the%20entry%20of%20one%20particular%20row%2C%20data%20subsequently%20added%20to%20the%20source%20file%20will%20not%20pull%20through%2C%20and%20the%20source%20file%20continues%20to%20work%20as%20normal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20see%20any%20error%20in%20that%20row%20or%20the%20source%20spreadsheet%20which%20may%20cause%20this%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHave%20tried%20rebuilding%20the%20destination%20file%20query%20from%20a%20new%20file%2C%20but%20I%20encounter%20the%20same%20error.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902354%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20connection%20troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422885%22%20target%3D%22_blank%22%3E%40neilingoe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20you%20didn't%20add%20row%2098%2C%20it%20somewhere%20in%20the%20middle.%20As%20I%20understood%20Power%20Query%20returns%20row%2097%20and%20all%20rows%20before%20that%2C%20as%20well%20as%20row%2099%20and%20all%20rows%20after%20it.%20Only%20this%20one%20is%20skipped.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902373%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20connection%20troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902373%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20you%20are%20correct.%20There%20are%20two%20queries%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%20A%20Source%20--%26gt%3B%20Sheet%20A%20Destination%20-%20Data%20up%20to%20row%20345%20included.%20346%20onwards%20not%20pulled%20through.%3C%2FP%3E%3CP%3ESheet%20B%20source%20--%26gt%3B%20Sheet%20B%20Destination%20-%20Data%20in%20Row%2098%20col%20M%20calculating%20correctly%20(formula%20in%20row%2098%20references%20row%20346).%20Data%20is%20destination%20file%20sheet%20B%20shows%20results%20as%20though%20sheet%20A%20row%20346%20does%20not%20exist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20as%20though%20the%20ability%20to%20pull%20data%20from%20the%20file%20was%20lost%20after%20that%20row%20was%20added.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902415%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20connection%20troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902415%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F422885%22%20target%3D%22_blank%22%3E%40neilingoe%3C%2FA%3E%26nbsp%3B%2C%20not%20sure%20what%20it%20could%20be.%20Perhaps%20you%20may%20provide%20copy%20of%20your%20file%20removing%20all%20sensitive%20and%20extra%20information%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-902440%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20connection%20troubleshooting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-902440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%20the%20whole%20file%20is%20sensitive%20data!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20there%20any%20other%20diagnostics%20I%20could%20run%20to%20get%20to%20a%20possible%20cause%3F%3C%2FP%3E%3CP%3E-%20The%20source%20file%20data%20is%20still%20within%20the%20same%20table.%3C%2FP%3E%3CP%3E-%20I%20have%20tried%20removing%20and%20re-entering%20the%20data%20manually.%3C%2FP%3E%3CP%3E-%20I%20don't%20want%20to%20recreate%20the%20source%20data%20table%20as%20it's%20being%20fed%20by%20a%20lot%20of%20automations%20and%20I%20would%20have%20to%20redo%20those.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20would%20cause%20a%20query%20to%20break%20but%20not%20give%20an%20error%20message%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
neilingoe
Occasional 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies