Power query refresh running unusably slow.

[SammMcCandless]

Hi all,

I have several data files in a standard format (xlsx files, 1 tab (all named the same), 4 columns of data each (all common headers(Owner, Supplier, Amount and Account)) - varying numbers of rows in each. These files are in a folder on sharepoint (no option to save these outside of here for this piece of work). All files together will be around 400k rows of data.

I have used power query to get data from this folder, and combine the files, then perform some further cleaning on the data, however the refresh rate is so slow (5+ hours before crashing out (I left it runnign one night just in case!)) that it is unusable. The same M code works on a much smaller sample (2 files of same format rather than 35, and fewer rows in each) so assume its not an error as such, but wondering if the slow rate is due to getting data from sharepoint, or something else I'm trying to do!

Any help greatly appreciated. Some screenshots/examples below (source data adv. edit is the file with the main work in it), but let me know if more info needed. Thanks

(ps. if I'm using list.buffer and table.buffer incorrectly, I will gladly take any tips, but the issue exsisted before including these, so taking out might not solve my issue)

 

[johnny51981]

Is the long refresh time a new development or has it been like that since you began creating this file?

I would try running the process for each of your 35 to make sure that they are individually successful. If successful, it's not a connection issue. If not successful, you have a corrupt or wrong file on your Sharepoint.
Also look at the 4 columns in each of your 35 sheets to make sure the entry of them is consistent in formatting. Could there be a Supplier populated in the Amount, just as an example.
After that, it would be a API/Connectivity issue. If you are able to access the files from the network folder they are housed on rather than through the Sharepoint link, that might be a better route for success.


Just the things that come to my mind. Also, I've stopped using Buffers. I've not seen much value when connecting to Excel or CSV files.

 

[SammMcCandless]

Is the long refresh time a new development or has it been like that since you began creating this file?

I would try running the process for each of your 35 to make sure that they are individually successful. If successful, it's not a connection issue. If not successful, you have a corrupt or wrong file on your Sharepoint.
Also look at the 4 columns in each of your 35 sheets to make sure the entry of them is consistent in formatting. Could there be a Supplier populated in the Amount, just as an example.
After that, it would be a API/Connectivity issue. If you are able to access the files from the network folder they are housed on rather than through the Sharepoint link, that might be a better route for success.


Just the things that come to my mind. Also, I've stopped using Buffers. I've not seen much value when connecting to Excel or CSV files.

Thanks - the steps I built in worked when the folder only contained 1 or 2 files, (I built it on a smaller number to test) and the issue arose when the folder contained all the files it does now - going through and checking each works properly is a good shout, will take a look at that, thanks.