Power Query in Excel slow

npanag

New Member
Joined
Jan 7, 2011
Messages
45
Hi,
I have an Excel file (in old format, let's say A.xls) that is produced every night from the backend databases and it contains 33 worksheets, that are lists (Power Query reads them as tables but with delay until it opens all tables). They contain records from a few dozens up to 10000 records. Let's say 33 summary tables up o some detail.

I am in another workbook that has two power queries on two worksheets of A.xls out of the 33. The method used in both of them is :

Source = Excel.Workbook(File.Contents(FilePath), null, true),
rgn = Source{[Name=WorkshhetName]}[Data],


I replaced the actual names with FilePath and WorksheetName.

When I say RefreshAll, it takes 5 to 10 minutes for these two queries.

Is there any way to define the specific 2 worksheets as a source to be refreshed, but not reading again all the 33 worksheets of A.xls?
The method Excel.Workbook(File.Contents(FilePath, null, true) unfortunately rereads all worksheets/tables when you need only one or two.

I saw a solution in Power BI, but I'm interested in Excel Power Query.
If I create two small pivot tables from A.xls then this solution is refreshed very quickly. Why not Power Query, too?

Thank you
Nick
 
Last edited:

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top