Power Query

[Anonymous]

Hi! I have been struggling to solve this issue and I'm hoping someone could help. I need to use a Query as a lookup file because it's much too large to constantly open up. The problem is my functions (Sumifs) won't work unless the Query is opened at the same time. In Excel, it holds the path when you break the files but it doesn't with Power Query and I can not find a solution. Perhaps I don't even know the right question to ask. Any help would be greatly appreciated. Thanks in advance.

Here is a sample string of the formula and I think it needs an actual file path but I can't seem to get it right:

SUMIFS(INDEX('MRO DLC v FS Query.xlsx'!DLCandFS[[SL BOP Inv Qty]:[FS EOP Inv Retail Amt]],,MATCH(Calculator!$J4,'MRO DLC v FS Query.xlsx'!DLCandFS[[#Headers],[SL BOP Inv Qty]:[FS EOP Inv Retail Amt]],)),'MRO DLC v FS Query.xlsx'!DLCandFS[Fiscal Year],Calculator!$D$3,'MRO DLC v FS Query.xlsx'!DLCandFS[Fiscal Week Number],">="&Calculator!$F$4,'MRO DLC v FS Query.xlsx'!DLCandFS[Fiscal Week Number],"<="&Calculator!$F$5)

[edhans]

You must have the target file in Excel open to refer to it with Excel functions. This changed with Excel 2007. With 2003 and earlier, it would read from the close file.

What you could do is create a new query in your destination workbook that summarized the results of the target file in to a managable dataset, or even the SUMIFS() calc itself and dropped a table in the destination file. Either that is the answer, or it is a supporting table for your actual SUMIFS function to operate on.

Then you can close the original file, open the new file, REFRESH ALL, and it will work.

A better practice would be to copy the query in the other file into this one, but summarize it. Other wise you have 3 links in your chain - Destination.XLSX->Target.XLSX->OriginalData. 

Destination.XLSX->OriginalData is better long term.