Data refresh is terribly slow

[chrisco]

I have a multiple workbooks fed by a text file (.txt). I'm using Power Query to manipulate into the proper format (filter, merge etc.) so that my Power Pivot workbooks can use it. (Think consolidated company and then individual for divisions)

The question I have is that when I have my data refresh it takes forever. I'm talking upwards of 10 minutes for 30k lines. Once it loads, the slicers and other functionality work appropriately.

The data I'm importing into my Power Pivot is relatively small (600k lines at the largest, 30k on smaller workbooks). The txt file is stored on a shared drive on the network, but I have the appropriate permissions (at least I believe it do - I eventually get the information).

Has anyone had similar problems?

 

[Matt Allington]

have you tried to put the file on a local PC folder and see what happens? My guess is the network is the issue. You could set up an auto replication of the file to a local folder using some syncing tool (I use Sync Toy but I guess Windows has some off line folder option still).

 

[chrisco]

Matt,
Thanks for the reply. Based on your recommendation, I moved the source file and the workbook onto my desktop and pointed Power Query to the new source file. Unfortunately, it seems to do the same thing.

The source file is 606,032 lines. When I update, the Power Pivot window rolls through like its updating and then seems to freeze at "Retrieved 600k lines". If I switch to the Excel window, I see that its "reading data" which seems to take forever. In fact, I've never actually waited it out. I've let it run overnight before though but I'm not sure how long it actually took. I have noticed if I hit ESC and cancel the read, save the workbook and re-open, now I have my workbook functioning the way I want it. This doesn't seem to be the "right" way though.

Any insight would be beneficial.