Forum Discussion
Power Query taking forever to load or refresh
Hi, Anonymous
It will depend on the number of times a source file (query) is duplicated or referenced and also the number of query transformations. The same file can be loaded multiple times.
Usually PQ would load the source data twice: First while retrieving the metadata to generate the evaluation plan and then to fetch the data itself.
You might considering using the M function Table.Buffer Table.Buffer - PowerQuery M | Microsoft Docs This can reduce the number of re-reading the base file.
You can find more performance tips on this blog here: Speed/Performance aspects – The BIccountant
Refer:
Why does Power BI "Load" much more data (volume) than what's in my source file?
XLSX file 10MB, but Power Query loads 100MB into Power Query
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
thank you for your answer, even without them , i still have a lot of files, they are necessary, it's a big report, all related to employee data (payroll, budget, headcout, learning, recruitment, turnover, straucture...)
OK, that being the case, I would see if I am using the same filter logic on more than one table. If 10 or 40 of your tables have the same few steps, like filtering to the last 6 months, or to a department, or remove nulls, or joins, or sorts, these can all be referenced from a single table.
See if you can replace Table.NestedJoin with Table.Join wherever you can, and with JoinAlgorithm.SortMerge if your keys are sorted ascending.
If there are a lot of files from a folder, see if you can make your transform file do the work for most of the queries. Avoid using a bunch of "or" statements to filter text values. Use inner joins wherever you can. Make buffered, distinct lists to filter other tables. Get rid of columns early. Reorder columns once. Use a parameter for TodaysDate so you can have one value for every query instead of calculating =Date.From(DateTime.LocalNow()) on every query.
Simplify your groups, and use the All Rows aggregation.
Make sure that your settings are set to Never let preview data download in the background.
And of course, keep optimizing. There is almost always a more efficient way to do it.
--Nate