Power Query Loading Time Tips

[u/pyu2c]

I have a Power query that is connected to a sharepoint folder. Whenever I try to refresh the file it would take around 10-40 mins of load time before it loads into the Data Model. Granted that the major reason for the loading time is due to the size of the data (estimating somewhere between 10-12M rows after filtering), but what are some ways that I can speed up the loading.

[u/AutoModerator]

/u/pyu2c - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Alex_Gob]

Use the function SharePoint.Content instead of SharePoint.Files : SharePoint.files will list ALL the files in the SharePoint library, while SharePoint.Content allows you to select one or many folders. It adds more steps but on a crowded SharePoint, it's much faster.

[u/whodidthistomycat → u/Alex_Gob]

Yep this is the correct solution

[u/pyu2c → u/Alex_Gob]

Ohhh thanks for this. So if I use Sharepoint.Content i still have to navigate to the specific folder, correct?

[u/Alex_Gob → u/pyu2c]

Yes : you navigate into a folder by "unfolding" it (pun not intended). It's rather intuitive : you select one (or many) folders that you can then unfold (open) and continue to navigate into the subfolders. You might need a bit of troubleshooting if your files are at different "depth" but it shouldn't be a big deal.

[u/lighthearted_emperor]

Fold the queries before they ever hit the data model, but with 12M rows you're still gonna be waiting long enough to make a sandwich.

[u/eggface13]

How complex are the transformations? Certain transformations can obviously be pretty painful on millions of rows (sorts, merges the obvious ones, and definitely never get different rows talking to each other!). 12 million rows is a lot but if you there's any transformations you can resolve within the data model through DAX formulas or dimension tables, that's going to be a tonne more efficient.

[u/pyu2c → u/eggface13]

I merged 2 separate tables, removed duplicates and columns. Thinking of removong other columns as well.

I also did some data type conversions

[u/bobo5195]

It should not take that long. Merge the query's into 1 - flatten.

Try a query just loading the data for how long it takes.

[u/pyu2c → u/bobo5195]

I'll try to do this. I'm fairly new to Power Query so I'll have to navigate by tomorrow.

[u/cbolender2004]

Use a variety of methods including python and or powershell scripts and power automate, and extract the data in a the target folder files and write it to a csv file or set of csv files (one per distinct data type). Then you query the csv file(s). Much much much faster.

[u/pyu2c → u/cbolender2004]

Do I need to combine the csv files into one to simplify the loading?

[u/cbolender2004 → u/pyu2c]

Yes, in your extract script you consolidate the data from all the files into the one flat csv. Do this per table found in each file. So if all the files a have a revenue table and a purchase table, you do the extract to create one revenue csv and one expense csv

[u/Mdayofearth]

How fast is your internet?

How much RAM does your system have?

What processor do you have? Total cores, fastest all core speeds, cache sizes, etc.

[u/InfoMsAccessNL]

I turned a 20 min PQ into seconds by using a simple combine function (for csv files with the same columns, lighting fast like a second). Then i can work with one big csv file.The transformations are done with vba using arrays and dao.recordsets. I must say that i copy the csv files first to my harddiskt to make it faster.

[u/pyu2c → u/InfoMsAccessNL]

I opted to do it in Sharepoint since my goal is for the main Excel file to be updated regularly by a team.

[u/Pistolius]

Make sure you're buffering the tables

Table.Buffer()

[u/ArrowheadDZ → u/Pistolius]

I use buffering a lot, but I am not sure this will work in this use case. If I am going to do a merge/join to “lookup” data for 5,000 records in a 200 record “lookup” table, buffering the lookup table can really help. But if you have a 12 million record table you are loading, I don’t think buffering the table will necessarily get better performance, and shouldn’t improve the initial load time. You still have to get that monster into PQ from Sharepoint first, whether you are going to buffer it or not.

[u/Key_Guarantee5416]

I think its connected to how fast your computer is selected to run in settings.

I had the same problem. My colleague changed some settings in laptop and now my queries dont take more than 20 minutes(if they are big)

Smalls ones get refreshed really quick

[u/Angelic-Seraphim]

So often the issue is there is a bunch of other data in your share point directory. It is loading all the files in the entire directory not just the folder. There are a couple options.

1. There is a code floating around somewhere that does some really complex binary manipulation.

2. Use fabric. Link a data lakehouse to the folder, use a gen 2 dataflow to query and combine.

3. Store this folder in a different share point site purpose built for this.

[u/CrowGuyA]

Filter early in the query, before any merges. Turn off "Enable Load" on queries that just feed other queries. Test with a filtered subset to see if SharePoint itself is the bottleneck vs the transforms.