Log In
Register
Home
Active Member
May 31, 2020
Offline
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
November 8, 2013
Offline
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.
Active Member
May 31, 2020
Offline
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.
November 8, 2013
Offline
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
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)
in
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.
Active Member
May 31, 2020
Offline
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.
November 8, 2013
Offline
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
Active Member
May 31, 2020
Offline
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.
November 8, 2013
Offline
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.
1 Guest(s)
