Power Query taking forever to load or refresh

[jelena2810]

Hello, 

My Power BI file is around 5mb, and I have a lot of queries (around 80) but majority of them is just couple of lines, as they are like lookup tables, for exemple year (2020, 2021, 2022). When I am in PB, I am able to quickly view every file I imported, but if I want to go to transform data, to go to Query, it takes litterally more then an hour for Query to load files, and then I just stop, because I can not wait any more and I try to open it again. So it takes more than an hour to load one query, so the only thing that is happening in that moment is just dots loading, and taking forever. Does anyone have any idea how can this speed up?

[RTumminaro]

Problem,

     Power Query In Excel refresh taking excessive long time to refresh (> 10 Minutes Power PC and > 30 Minutes thin client)

Systematic Investigation

In Queries & Connections refreshed each table.  Of 29 Tables ( none more than 3,000 lines) found one table taking long time to refresh. All of my tables have filters.  This particular table had Grouped columns.  I used Grouping to quickly hide and unhide columns when hunting for data in tables. 

Solution

Removed Grouped Colums and problem solved.  Not sure if filters affect refresh time (Not that I have noticed) 

Lesson
No Grouping in Tables

[v-angzheng-msft · Accepted solution]

Hi, @jelena2810 

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

Speed/Performance aspects

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.

[jelena2810]

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...)

[watkinnc]

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

[watkinnc]

I'm going to guess that it's the 80 queries. Is it strictly necessary? Can't you use report filters or DAX for some of that?

--Nate