Excel – How to reference a cell’s value in PowerQuery

.mexcelparameter passingpowerquery

I'm having multiple PowerQuery queries that I would like to feed the value of a cell in my Excel file. In this particular case, the full path to the sourcefile name.

Is there any way I can get this into PowerQuery?

Best Answer

This can be achieved using a named range and a custom function in PowerQuery:

  1. Name the cell you need to refer (type in a name into the file left of the formula bar) - e.g. SourceFile
  2. Insert a new blank PowerQuery query (PowerQuery ribbon -> From other sources)
  3. In the PowerQuery editor, go to View -> Advanced Editor and paste the following code;
(rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]
  1. Name the query to GetValue (Name property in the Query settings pane on the right)

Now you can access the named cell in your queries, using GetValue(cellName) - e.g. 

= Excel.Workbook(File.Contents(GetValue("SourceFile")))