How can I reference a cell’s value in PowerQuery

by

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

More Related Contents:

  1. PowerQuery: How can I concatenate grouped values?
  2. Convert matrix to 3-column table (‘reverse pivot’, ‘unpivot’, ‘flatten’, ‘normalize’)
  3. Excel UNIQUE Across Columns
  4. Excel: How to gather unique values in one column that are associated with duplicates in another column?
  5. VBA comma separated cells, needt last entry only
  6. I have an Excel data sheet that I would like to extract data from into a new sheet
  7. Application not quitting after calling quit
  8. Open Excel file for reading with VBA without display
  9. Wait for shell command to complete [duplicate]
  10. How to return the number of dimensions of a (Variant) variable passed to it in VBA [duplicate]
  11. How to add a named sheet at the end of all Excel sheets?
  12. Find last used cell in Excel VBA
  13. VBA general way for pulling data out of SAP [closed]
  14. Does Dir() make any guarantee on the order of files returned?
  15. Call a Sub with a String
  16. Export hashtable to CSV with the key as the column heading
  17. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  18. ADODB Connection String for .csv
  19. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  20. Pivotfields multiple filter
  21. How to send mail when the .Send does not work?
  22. Get text from clipboard using GetText – avoid error on empty clipboard
  23. Benchmarking VBA Code
  24. Pandas read_excel with Hyperlink
  25. Unpivot an Excel matrix/pivot-table?
  26. Display milliseconds in Excel
  27. Application.Quit command not closing the entire Excel Application
  28. Trying to open the workbook in separate instance
  29. Function Overloading and UDF in Excel VBA
  30. Excel, How to split cells by comma delimiter into new cells

Leave a Comment