0

I have an excel file that uses many identical power queries to process different data which is in the same format. Each data set has ten rows of data (ten samples), and the different data sets represent different experimental parameters (ten parameters).

So I have a master workbook that uses power query to import the ten data files, process them, and load them into individual tables which I then use to plot and analyze the data.

My problem is, after some unknown event, Power Query will no longer display the data in the query editor, it just hangs on the loading screen. In scouring forums, I have found if I duplicate the query, the data will appear, but only in the duplicate and not in the original. After a while, even the duplicate develops the same challenge. This happens on some, or all of the queries. I cannot explain it at all. Each file has the exact same format and types of data. 2500 columns x 10 rows.

Here is some additional info:

  • All files are stored on a shared dropbox folder.
  • Sometimes, I can 'refresh the data' and load it from excel directly. Actually I can often do this, I just can no longer manipulate the data in the query editor as it will not load.
  • sometimes duplicating the query works, and sometimes it does not.
  • once it will not load, that seems to be the end of it. Even if I change the 'source' in step one of the query to a known file that loads, it will not.
  • In power query, when a file will not load (which has previously loaded) if i select the 'Source', aka step one in the query, it should display the raw data as imported prior to any processing. It still hangs on loading which makes me beleive it is not in any of the processing code. Also, the fact that the exact same query and files previously loaded fine.
  • sometimes I can close the power query when it will not load, even with a new file import (changing the source on a duplicated query), and 'load to' directly in excel.

Here are some screen shots: enter image description here

Not a very descriptive image, but essentially its just a blank screen with the loading dots whipping by the screen at the top. If I change the Source, after clicking OK, it hangs there with the loading icon swirling within the OK button.

I have tried moving files to the desktop (so not a shared folder), I have tried changing the source files and the contents of them. I have tried a lot... the results are described as above.

Updated Details: With a recent attempt at debugging, I have done the following:

  • The power query preview gets 'stuck' loading (at any step in the query processes)
  • I have cleared the cache and also increased it significantly.
  • At this point, the query is still stuck.
  • Close, save, and reopen the excel file.
  • Still stuck. The cache reports 0 bytes used (despite some querys are displaying the preview?)
  • Duplicate the query with no changes. It will either load, or Does not load....
  • Close and save the excel sheet. Reopen. Query preview loads.
Improve this question
7
  • What happens if source files are NOT in dropbox folder? Commented Jul 7, 2023 at 18:25
  • If all files are located on the Desktop, I get the same behaviour. Commented Jul 7, 2023 at 19:34
  • It sounds like perhaps you are looking at the data preview, which would usually not change even if the source changes, unless you clear the preview cache (or duplicate the query). That doesn't mean the output of the powerquery wouldn't be different if you refreshed it, just you stepping through it Commented Jul 7, 2023 at 19:35
  • Yes - I am looking at the preview I believe. In order to edit the query, such as to add further steps, I require the preview to load. For example, if I want to remove columns, I need to select the columns to remove. It is problematic that the preview does not currently load. If I can clear the preview cache that might help, and would prevent further problems created from consistently duplicating the query. Commented Jul 7, 2023 at 19:55
  • What happens if you clear the PQ cache completely? Commented Jul 9, 2023 at 19:58

1 Answer 1

Reset to default
0

I think I resolved this by adding another step to the query to change the data type to decimals. It was not detecting them all as decimals throughout the whole query.

I also discovered that the file was topping out at 5 queries, after which it would actually erase all of the queries somehow and corrupt the file. The queries and connections pane would report 0 queries when there was really 5+. I was able to save it by opening the 'existing connections' dialog and deleting the last query. Bit of a separate issue but I thought i would share it.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.