Forum Discussion
power query refresh really slow- excel file
Hi ddm123,
I find a similar post for this problem, you could try to refer to Any way to speed up really slow refresh times in Power Query? | Power Query | Excel Forum for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Anonymous
I undestand. Then I can't figure out how these 4 lines of everey sheet are removed after you've combined them.
I would go for
- Create one query that threahts your file like you need it
- Create a parameter with one path/name of a sample path
- replace the filename in your first query with the parameter and convert it to a function
- use the data source to extract the file names of this folder... in case do some filtering
- add new column where you use your funciton and path and name as input.
That should do the trick
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy
Hello Anonymous
very difficult to get an idea what exactly happens here.
- Columns were expanded and without use deleted afterwards,
- by extracting a plain xlsx-file i don't get how a column reference can be a Table in this case for "Table.Skip" on row level - SkipRows = Table.AddColumn(#"Removed Columns2","Skip first 4",each Table.PromoteHeaders(Table.Skip([Custom.Data],4))),
- a customfunction is used (not knowing what it does) - maybe use table.buffer in there
- I don't know if Table.Buffer at the end is very usefull
Maybe a change of concept in retrieving data would be best (maybe to create function to query the files, and put the data together at the end other change logic - difficult to answer if the real goal is not known.
BR
Jimmy
Hi Jimmy801 ,
thanks so much for the response. I put the "Table.Skip" because for all the files i'm combining the first 4 rows were not needed to transfer to the query, also the cutsom function was used to remove all the empty columns for the files. I will try to create a function to connect the data to see how much of the difference it will make. Each file contains like over 100 columns and by using power query I'm trying to combine the data together as the files in the folder will be added on a monthly basis, then build a data model out of the flat files with 100 columns here.
I'm just so frustrated that it takes so long to combine the files every time I refresh the data model. One other thing is it seems like for every step added on PQ, the data was retrieved from the very first step that I don't really understand why and I think that's another issue that causes the data super slow to load. by adding Table.Buffer step I thought the purpose is to cache the table in the memory to accelerate the data load. However, I realized that when adding a step after the buffer step, the data was still pulling from the be beginning... Am I understand how table.bufffer works wrong?
Hello Anonymous
I undestand. Then I can't figure out how these 4 lines of everey sheet are removed after you've combined them.
I would go for
- Create one query that threahts your file like you need it
- Create a parameter with one path/name of a sample path
- replace the filename in your first query with the parameter and convert it to a function
- use the data source to extract the file names of this folder... in case do some filtering
- add new column where you use your funciton and path and name as input.
That should do the trick
If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun
Jimmy