How can I reference a cell's value in PowerQuery

excel parameter-passing powerquery m
Peter Albert picture Peter Albert · Nov 18, 2014 · Viewed 74.5k times · Source

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?

Answer

Peter Albert picture Peter Albert · Nov 18, 2014

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")))

Related questions

Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

I'm trying to get data from an Excel file on a button click event. My connection string is: string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\source\\SiteCore65\\Individual-Data.xls;Extended Properties=Excel 8.0;"; When I click on the button, …

Read More