Power Query: How to Retrieve Excel Defined Name Values

This article covers defining a named value in Excel and creating a reusable Power Query function to retrieve it, allowing specific data to be referenced from a workbook and used in queries for tasks like filtering or analysis.

Instructions

Step 1: Define a Named Value in Excel

1. Select the cell containing the value to name in Excel.
2. From the Formulas tab, click Name Manager.
3. In the Name Manager dialog box, click the New button.
4. In the New Name dialog box:
   • In the Name field, enter a name for the cell (for example, REF_NAMED_RANGE_VALUE).
   • In the Refers to field, confirm or select the cell with the reference data.

5. Click the OK button. The new name will appear in the Name Manager.

6. Click the Close button.

Now, when selecting the cell in Excel, the defined name will appear in the Name Box next to the Formula Bar.

Step 2: Create a New Power Query Function

1. From the Home tab in Power Query, select New Source > Other Sources > Blank Query. This creates a new blank query.
2. From the Home tab, click Advanced Editor.
3. Replace the default query with the following function:

let
  GetDefinedNameValue = (definedName) =>
    let
      name  = Excel.CurrentWorkbook(){[Name = definedName]}[Content],
      value = name{0}[Column1]
    in
      value
in
  GetDefinedNameValue

4. Click the Done button.
5. In the Query Settings pane, under Properties, rename the query to GetDefinedNameValue.
6. Click Close & Load to exit Power Query.

Results

To test the function:

1. From the Queries pane in Power Query, select GetDefinedNameValue.
2. A prompt will appear asking for a parameter. For definedName, enter REF_NAMED_RANGE_VALUE. The function call will look like this:

= GetDefinedNameValue("REF_NAMED_RANGE_VALUE")

3. Click the Invoke button.
4. The result of the function will appear as a new query named Invoked Function in the Queries pane, returning the value TEST PROJECT as expected.

This function can now be used to easily pull specific reference values from an Excel workbook into other queries for filtering or other tasks.

Summary

This process defines named values in Excel and creates a reusable Power Query function to access them, making it possible to pull specific reference values into other queries without modifying the underlying query logic when the reference data changes. The result is more efficient and consistent data analysis.