Power Query in Excel Error - There's a problem with the clipboard, but you can still paste your content within this workbook.

Question

I have one file that was previously working, but now repeatedly gives me this error message when I click Refresh All

When the message pops up, there is data on the clipboard which I can paste into NotePad - it looks like the entire column of data on a couple of columns - mostly one date column but it changes at least once between error messages.

When would Power Query move any data onto the Clipboard? Is this intended behaviour in some scenario!?

According to my Company Portal I have the latest version of the "Microsoft 365 Apps for enterprise" installed Version 2008 (Build 13127.21348 Click-to-Run)

Answer 1

Hi Jon,

As far as I know the loading query or refresh query won't use the clipboard.

And I've tested it on my side, with Clipboard opened. Refresh All did not add anything to the clipboard.

In your case, could you open the clipboard as well, and click on the Clear All button if there are any items there?

Then try refresh your query again, see if that make any difference.

My Excel version is not the exact same one as yours, I will try reproducing the issue you described under the same version and get back to you. 

Meanwhile, it's also recommended to usesafe modeto open the Excel, in this way we can rule out the add-ins or Macros in your Excel.

Regards,

Alex Chen

Answer 2

Hi Jon,

I've tested the version 2008 (Build 13127.21348) Semi-Annual Channel. 

The test result is the same.

Could you confirm if the safe mode or clear clipboard work on your side?

Regards,

Alex Chen

Answer 3

Hey Alex, thanks for the reply. 

So I've opened the file from scratch and have checked the Clipboard to see that am starting with "Clipboard empty" and tried pasting into NotePad and get nothing.

I then hit Refresh All to trigger the Power Query refresh. It steps through "Loading Data Model",  "Connecting to datasource..." reading data briefly flashes up and then I get the first error popup. At this point, I switch over to NotePad and can paste an entire column of data.

I click OK to clear the dialog until it pops up again, then the same column of data is on the clipboard. I click OK again, and then a different column of data is on the clipboard.  21 times it has the one column, then on the 22nd time it's the other column again.  another 20 times and it finally stops showing the error.

So I'm sure it's Power Query putting things on the clipboard, I just can't work out why!!!

Any help would be appreciated.

Answer 4

OK - I missed the comment about Safe Mode - yes, it works without error in safe mode!

Looks like the only Active Application Add-in is the Microsoft Azure Information Protection COM Add-in

Answer 5

Hi Jon,

If the safe mode works, then the issue could be related with the Macro and Add-ins, just like we discussed in the previous posts.

Besides the COM Add-ins, there is one more place you need to check, Go to Excel > Insert > Add-ins > My Add-ins, is there any Add-ins listed there?

Also, does this worksheet contain any VBA Macros?

Regards,

Alex Chen