Power Query Opens Source Workbook on Refresh

[Mahyar]

Hi, 

I have been experiencing an issue that when I refresh the query it opens the source workbook that it is refrencing. When I close the workbook it opens it again and again and again. It continues to open the workbook even after I stop the refresh.

[Mahyar · Accepted solution]

I believe it was short lived bug.

[msoehnchen]

I am expierencing the same issue.

I have a power query which is referencing two seperate excel-sheets and one of them (always the same one) is opened automatically, over and over again.

cant figure out why.

[rpayne86]

Hey, 

I'm experiencing the same issue. Did you ever find a solution?

Thanks

[msoehnchen]

No, but that was somehow tied to that specific workbook. After creating a new one and put the plain data from the old one into it, it worked fine. So, thats some setting in the workbook i guess.

And, not sure if this matters or not: that "problematic" workbook came from the asian side of the world whereas i am located in Europe. 

[rpayne86]

I've done some more thorough testing, and have narrowed it down to XLSB files. If the source for the Powerquery is an XLSB, and if another user is in that file, it decides to spring open a copy of that file (as read only).

If another user doesn't have the file open, it typically refreshes ok.

If you change the file to an XLSM (Or probably XLSX), the issue does not occur whether another user has the file open or not.

As it happens I believe PQ pulls in the latest SAVED version of the file, not the Live copy, and so I'm confused why read/write even makes a blind bit of difference.

We bounced round ideas that perhaps because the XSLB does not use the openXML standard that this may be why it causes issues.

[jyerram]

I tried one whole day on the issue and tried several things suggested on the internet. This is the only solution that really worked, I am amazed again at how simple, silly things matter again...

[cstreet]

I have noticed the same behaviour today when the source files are .xls files. I previously never seen this happen with the same .xls files prior, it just started doing it today. I did notice a team member had some files opened. After they closed the .xls source files the issue was resolved.

[rpayne86]

Yeah i pondered this, but it forms part of a complicated web of files which a lot of the business uses to 'run the business'. I'm also planning to move it to PowerBI soon™, so reluctant to change if i can avoid.

Thanks, i'll see if the user can deal with it for the mean time.

[Anonymous]

@Mahyar,

Is your workbook excel? Can you  share a screenshot of what is showing you?