Power Query: Load Excel Data from Local, Web, or Current Workbook

If you’ve spent any meaningful time with Power Query, you’ve probably noticed that one of the most frustrating parts isn’t building transformations; it’s just getting data in. Every time I need to load data from an Excel file, I run through the same mental checklist: Where is the file? How is the data structured? Which function do I need?

My confusion comes down to one limitation: Excel.CurrentWorkbook() cannot read plain worksheet data. It only works with Excel tables, defined names, and dynamic arrays. That single constraint forces me to think about file paths, web addresses, and a completely different set of functions as soon as the data lives on a plain sheet.

This guide covers two main scenarios: loading data from the current workbook and loading data from a local or web-based Excel file. Within each scenario, examples are provided for worksheet data, Excel tables, and defined names. The one exception is worksheet data in the current workbook scenario, which is not supported by Excel.CurrentWorkbook() and is addressed in Scenario 2 instead.

Scenario Data Overview

The examples in this guide use a small dataset to illustrate each method. The data contains three columns: Type, Flavor, and Quantity. Each row represents a unique item, with the Type and Flavor columns containing text values and Quantity containing numeric values.

Scenario 1: Loading Data from the Current Workbook

Excel.CurrentWorkbook() retrieves data from the workbook where the query itself is stored. This does not mean the currently active open workbook; it specifically refers to the workbook containing the query, which may be different from whatever file you have open at the time.

One practical benefit of this function is that it continues to work even if the file is renamed or moved. That makes it a good choice for files that change names regularly, like dated reports. The trade-off is that it cannot read plain worksheet data, so your data must be structured as an Excel table or defined name.

Method 1: Worksheet Data

Not supported. Excel.CurrentWorkbook() does not return plain worksheet data. If your data is on a plain sheet, use Scenario 2 instead.

Method 2: Excel Table

let
  Source = Excel.CurrentWorkbook(), 
  TBL_EXAMPLE_DATA = Source{[Name = "TBL_EXAMPLE_DATA"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(
    TBL_EXAMPLE_DATA, 
    {{"Type", type text}, {"Flavor", type text}, {"Quantity", Int64.Type}}
  )
in
  #"Changed Type"

Method 3: Defined Name

let
  Source = Excel.CurrentWorkbook(), 
  RNG_EXAMPLE_DATA = Source{[Name = "RNG_EXAMPLE_DATA"]}[Content], 
  #"Promoted Headers" = Table.PromoteHeaders(RNG_EXAMPLE_DATA, [PromoteAllScalars = true]), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {{"Type", type text}, {"Flavor", type text}, {"Quantity", Int64.Type}}
  )
in
  #"Changed Type"

Scenario 2: Loading Data from a Local or Web-Based Excel File

When your data comes from an Excel file stored on a local drive, network drive, SharePoint, or any web-accessible URL, the query structure is identical. The only difference is the function used to point Power Query at the file.

NOTE: For local and network files, use File.Contents() with the full file path. For SharePoint and web-based files, use Web.Contents() with the full URL. In both cases, the result is passed to Excel.Workbook() to parse the contents.

How to Get the File Path or URL

1. From the File ribbon in Excel, click Info.
2. Click Copy path.
3. Remove ?web=1 if it appears at the end of the path.

NOTE: If the file is renamed or moved, the query will break and must be updated.

To switch from a local file path to a web-based file path, replace:

File.Contents("C:\path\example.xlsx")

with:

Web.Contents("https://path/example.xlsx")

Method 1: Worksheet Data

let
  Source = Excel.Workbook(File.Contents("C:\path\example.xlsx"), null, true), 
  Sheet1_Sheet = Source{[Item = "Sheet1", Kind = "Sheet"]}[Data], 
  #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars = true]), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {{"Type", type text}, {"Flavor", type text}, {"Quantity", Int64.Type}}
  )
in
  #"Changed Type"

Method 2: Excel Table

let
  Source = Excel.Workbook(File.Contents("C:\path\example.xlsx"), null, true), 
  TBL_EXAMPLE_DATA_Table = Source{[Item = "TBL_EXAMPLE_DATA", Kind = "Table"]}[Data], 
  #"Changed Type" = Table.TransformColumnTypes(
    TBL_EXAMPLE_DATA_Table, 
    {{"Type", type text}, {"Flavor", type text}, {"Quantity", Int64.Type}}
  )
in
  #"Changed Type"

Method 3: Defined Name

let
  Source = Excel.Workbook(File.Contents("C:\path\example.xlsx"), null, true), 
  RNG_EXAMPLE_DATA_DefinedName = Source{[Item = "RNG_EXAMPLE_DATA", Kind = "DefinedName"]}[Data], 
  #"Promoted Headers" = Table.PromoteHeaders(
    RNG_EXAMPLE_DATA_DefinedName, 
    [PromoteAllScalars = true]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {{"Type", type text}, {"Flavor", type text}, {"Quantity", Int64.Type}}
  )
in
  #"Changed Type"

Appendix: Structuring Data in Excel

If the data is already structured as a worksheet, table, or defined name, you can skip this section. It is included here as a reference for anyone who needs to set up their data before connecting Power Query.

Plain Worksheet Data

Plain worksheet data is the simplest form: data sitting on a sheet with no special formatting or naming applied. In the examples throughout this guide, the data lives on a sheet named Sheet1. This is the most common way data ends up in Excel and, as noted above, the most limited when working with Excel.CurrentWorkbook().

Creating an Excel Table

To create an Excel table:

1. From the Insert ribbon in Excel, in the Tables section, select Table.
2. The Create Table dialog box is displayed. Verify that the referenced data set is correct. If the table includes column headers, verify that the My table has headers checkbox is enabled.
3. Click the OK button. The selected data has been converted to an Excel table.

The new table is given a default name which is usually the label Table with a numeric suffix (e.g., Table1 in this example).

The table name can be changed from the Name Manager:

1. From the Formulas ribbon in Excel, in the Defined Names section, select Name Manager.
2. The Name Manager window opens.
3. Select the appropriate entry and click the Edit… button. The Edit Name window is displayed.
4. In the Name field, change the displayed name to a new name.
5. Click the OK button when complete.
6. Returning to the Name Manager window, the new name is now displayed in the Name column. Click the Close button to exit the Name Manager window.

Creating a Defined Name

Defined names let you apply a label to a cell range without converting it to a table. This is useful when you want named references for use in formulas or Power Query without changing how the data looks or behaves.

To create a defined name:

1. From the Formulas ribbon in Excel, in the Defined Names section, select Name Manager.
2. The Name Manager window opens. Click the New… button. The New Name window is displayed.
3. In the Name field, provide a new name for the data range. Verify that the Refers to field references the correct data range including column headers where appropriate.
4. Click the OK button when complete.
5. The Name Manager window is displayed with the newly defined name. Click the Close button to exit the Name Manager window.

Summary

Loading Excel data in Power Query comes down to two decisions: where the file lives and how the data is structured. Use Excel.CurrentWorkbook() when the data is in the same workbook as the query and formatted as a table or defined name. Use File.Contents() for local files or Web.Contents() for SharePoint and web-hosted files, both of which support worksheets, tables, and defined names, and both of which follow the same query structure. Understanding this framework will save you from relearning it every time.