Excel Forum

Really Slow PQ Refresh

Aravind Krishnakumar

Active Member

Members

Forum Posts: 5

Member Since:
May 31, 2020

Offline

May 31, 2020 - 10:22 pm

Could someone help me my PQ refresh times.

i tried the background data Turn OFF method, with a little improvement.

May be the way i make the query is not efficient.

I have 30-40 workbooks each with 30-40 sheets.

am just trying to combine everything and put into a pivot table.

The query works, just that it takes 5-7 mins even after the background is off.

am attaching these

1. query file

2. Test File (which needs to be cleaned first)

Hope someone could help me out.

thanks in advance

Avi

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 2, 2020 - 2:06 pm

Hi Aravind,

I was able to do a full refresh in under 1 minute, but I guess it's just the difference in computer performances.

You are using power pivots, the best option is to add Actual and Spare tables in the data model and relate them to Shots, instead of merging those 3 tables in power query, PP is much faster.

Also, the date table should not have missing days.

Best is to build a date table like this:

Min = Number.From(List.Min(CombinedData[invoice date])),
Max = Number.From(List.Max(CombinedData[invoice date])),
Source = Table.FromList({Min..Max}, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Replace the red date column with yours.

Aravind Krishnakumar

Active Member

Members

Forum Posts: 5

Member Since:
May 31, 2020

Offline

June 3, 2020 - 12:47 am

Thank you catalin,

The table about the date, is to be created using a blank query right? And just paste it?

Sorry for not knowing what to do..

BTW, how was that less than a minute.. it takes over 3 mins in my computer and mine is fast.. with a lot of RAM and CPU power..

You saw that my sheets structure is way off, these are created by different departments and they send to me.. so I wanted to automate my work.

What I tried today was not use the promote headers, instead I put in the headers myself. That reduced another 30sec.

Power pivot is faster but am just confused on why it is not fast in my computer.

Just a thought.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 3, 2020 - 4:29 am

Aravind Krishnakumar said

The table about the date, is to be created using a blank query right? And just paste it?
Sorry for not knowing what to do..
Power pivot is faster but am just confused on why it is not fast in my computer.

Yes, a blank query.

Go to advanced editor, and use this code:

let

Source

Make sure you replace the red text with your query references.

This is just the start, you can continue with adding year, month, quarter columns and any other date detail needed, using the PQ interface.

There are many things that can affect performance, not just RAM and CPU. For example, 32-bit or 64bit office can make a difference. I have a 64 bit office installation, but in the same time, the operating system and all programs, including office, are running on SSD, not HDD, this adds a significant performance improvement, at least on my computer.

Once you remove the table merging in power query and use power pivot relationships, you should see a significant improvement.

Aravind Krishnakumar

Active Member

Members

Forum Posts: 5

Member Since:
May 31, 2020

Offline

June 8, 2020 - 10:20 pm

Dear Catalin,

Thank you very much for your input.

i removed the merger table and used the relationships.

i also created custom function to read available sheets and which sheets I want.

also another function to process each sheet.

But I am stuck,

if I want to change something on a sheet, I just have to change it in the single sheet - but the function I created doesn't pick up the changes.

so I need to update the function everytime I make some changes.

is there a way to dynamically change the codes inside the function?

Hope you don't mind taking a look at the file.

The Date query - I understood how to do, but the attached file I haven't updated.

thank you again for your input.

These does help me a lot.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 9, 2020 - 8:12 pm

The changes you make in a sheet are not reflected instantly in power query and in power pivot.

You need to update the queries: Datatab-RefreshAll

Aravind Krishnakumar

Active Member

Members

Forum Posts: 5

Member Since:
May 31, 2020

Offline

June 11, 2020 - 1:55 am

I was wondering about functions I create.

Please correct me if am wrong.

What I did was,

made a function using a query with parameters, this created a folder group. When I opened the function and clicked advanced editor, I saw that the code inside the function is same as the query used to make the function.

Then I I had to modify the query, but the function Didn't change.

So Everytime if I had to make some changes, I have to manually make the changes to fuction as well as the query?

Or does the function take takes up all the changes to the query after the refresh!

I checked the refresh, and after invoking the function, the changes which I had made to the query(function) didn't happen! Which means the function was still old and the changes didn't happen inside it.

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

June 11, 2020 - 4:01 am

I have no idea what changes you have to do in the function. Usually, you have to avoid using hard typed file paths, or column names. Use generic functions, for example you can use Table.ColumnNames instead of hard typed lists.

Without examples to see what you have to do, I can only provide generic answers as well.