Speed-Up efficiency of workbook

[mickeystanford_alumni]

Hi all,
I have an excel file of 16,000KB. It is not huge but it goes really slow...
I would like to know how to improve the efficiency of the file (I already have file in manual, so need something more sophisticated).

I have a bunch of power queries connected through a dynamic path (this slows down the file for sure, but I need it as I do create a summary report by bringing different sheets from different excel files here...so maybe there is a way to improve the efficiency here)?
The code I use on the advanced editor is this:
let
ParametersTable = Excel.CurrentWorkbook(){[Name="ParametersTable"]}[Content],
UserName = ParametersTable{0}[Column1],
FilePath = "C:\Users\" & UserName & "\xxxxxx.xlsm",
Source = Excel.Workbook(File.Contents(FilePath), null, true),
// Specify the sheet to import
xxx = Source{[Item="xxx",Kind="Sheet"]}[Data]

in
xxx

Then the rest is I have some indirect formulas linked to Name Manager columns (e.g. $F8,$G8,$H8 is a column called SALES.US.CARS) =SUMIFS(INDIRECT(TEXTJOIN(".",TRUE,$F8,$G8,$H8)),INDIRECT(TEXTJOIN(".",TRUE,$F8,$G8,$H$2)),DATE($I8,M$6,1))

Is there any recommendation?

Thank you very much .

 

[shift-del]

Thank you very much .

Get rid of INDIRECT(). It is a volatile function. Every INDIRECT()-formula gets recalculted when a calculation is triggered.

 

[mickeystanford_alumni]

Thank you. What is the best way to get rid of the indirect() ? I wanted to use the same formula for all sheets and they reference to other cells all in the same position in the excel file.

 

[shift-del]

Since you are already working in PQ you should do those calculations also in PQ.

 

[mickeystanford_alumni]

Since you are already working in PQ you should do those calculations also in PQ.

Well actually I create a sheet based on the PQ as it is a matrix I create for visualization purposes, so for sure I need to bring a formula there...

Any helps please?

 

[shift-del]

I have no further ideas.