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
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:
