PowerQuery How can I reference a cell s value

Question

I would like to feed the value of a cell in my Excel file but I've multiple PowerQuery queries. 

In this example, the full path to the source file name.

How can I get this into PowerQuery?

Comments

Thank you for the solution. Appreciate it!

Answer 1

You can do so using a named range and a custom function in PowerQuery:

1. Name the cell you need to refer - e.g. SourceFile
2. Insert a new blank PowerQuery query 
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

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

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

Comments

Yeah!!!

It works.

Thanks