Power Query: How to Retrieve Excel Defined Name Values

In this guide, we will walk through the process of defining a named value in Excel and creating a reusable Power Query function to retrieve that value. This will allow you to easily reference specific data from your Excel workbook and use it in queries for tasks like filtering or analysis.

Instructions

Step 1: Define a Named Value in Excel

1. Select the cell containing the value you want to name in Excel.
2. From the Formulas ribbon, 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 (e.g., 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 you select 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 ribbon in Power Query, select New Source > Other Sources > Blank Query. This creates a new blank query.
2. From the Home ribbon, 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

You’ve learned how to define named values in Excel and create a reusable Power Query function to access them. This process makes it easier to pull specific reference values into other queries without modifying the underlying queries when the reference data changes. These steps will improve the efficiency and consistency of your data analysis.