Power Query to Access Excel Data from Current Workbook, Local File, or SharePoint

Why does it always feel like the hardest step when using Power Query is simply loading data? I have written a gazillion queries (approximate) in Power Query, however, each time I need to load data from Excel, I need to work through a mental flow chart to figure out how to do it. This flow chart is based on where the file is located (local file vs. SharePoint / web-based file) and how its data is internally structured (simple worksheet vs. Excel Table vs. Defined Name). The last time I checked, both Excel and Power Query are Microsoft products so I would expect them to be easier to connect.

I think the complication is caused by one primary issue:

The function Excel.CurrentWorkbook() is not able to retrieve simple worksheet data. The function only accesses data structured as an Excel Table, Defined Name, or dynamic array. Now, for various reasons, Excel data may need to be in a simple worksheet so you’re then forced to think about local file paths and web-based addresses as well as an entirely different set of functions to retrieve the data.

In this article, accessing Excel data from the current workbook, a local file, or a SharePoint / web-based address using Power Query are demonstrated. Each of those location scenarios are further detailed with examples using Excel data structured as a worksheet, table, or a defined named range.

Structuring the Data

Worksheet Data

In this article, the example data is located on a simple worksheet using the default Sheet1 name. If you are already familiar with creating Excel Tables and Defined Names, then feel free to skip ahead to the various Power Query data retrieval scenarios.

Create Excel Tables

On the data worksheet, select the column headers and data to include in the new table.

From the Insert ribbon, in the Tables section, select Table.

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 checked.

Click the OK button. The selected data has been converted to an Excel Table.

The table is formatted according to the default Table Style which can be changed from the Table Design ribbon in the Table Styles section.

The new table is also 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. From the Formulas ribbon, in the Defined Names section, select Name Manager.

The Name Manager window opens. Click the New… button.

Select the appropriate entry and click the Edit… button.

The Edit Name window is displayed.

In the Name: field, change the displayed name to a new name.

Click the OK button.

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.

Create Excel Defined Names / Ranges

As an alternative to Excel Tables, Excel has a feature called Defined Names which applies a label to a cell or a range of cells without converting those cells to a table. The defined name may then be referenced in formulas to refer to the underlying range instead of using cell references. This feature makes formulas easier to read and maintain over time.

On the data worksheet, select the column headers and data to include in the new defined name.

From the Formulas ribbon, in the Defined Names section, select Name Manager.

The Name Manager window opens. Click the New… button.

The New Name window is displayed.

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.

Click the OK button when complete.

The Name Manager window is displayed with the newly defined name. Click the Close button to exit the Name Manager.

Using Power Query to Get Data from Excel Files in Various Locations

In the following sections, Power Query is used to retrieve the example data from an Excel workbook located in three possible location scenarios:

1. Current workbook using the Excel.CurrentWorkbook() function
2. Local workbook using a local computer disk path
3. SharePoint / web-based address

Within each location scenario, Power Query data retrieval is further demonstrated with three retrieval methods:

1. Worksheet data (not structured as a table or defined name)
2. Excel Table
3. Excel Defined Name (range data)

Scenario 1 – Retrieving Data from the Current Excel Workbook

In this scenario, the function Excel.CurrentWorkbook() is used to retrieve data from, as you may guess, the current workbook. The function name itself is a bit confusing since it refers to the current workbook that the Power Query query is stored within. It doesn’t refer to the current active open workbook which may be an entirely different file, i.e., not where the query is stored within. Also, for reasons I don’t understand, Excel.CurrentWorkbook() is not able to retrieve simple worksheet data from the current workbook. The data must be structured as an Excel Table or a Defined Name for the function to access it.

One of the benefits of using Excel.CurrentWorkbook() is that the query continues to function even if the file’s name or location are changed. Using this function reduces the need for query maintenance if the file is likely to be renamed, e.g., daily reports with a date appended to the file name.

Method 1: Worksheet Data

This retrieval scenario and method is not possible. As I mentioned in the scenario introduction, Excel.CurrentWorkbook() returns the contents of the current Excel workbook. However, it only returns tables, named ranges, and dynamic arrays. It does not return worksheets.

Method 2: Table Data

In this method, Excel.CurrentWorkbook() is used to retrieve data from the current workbook where the data is structured as an 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 / Range Data

In this method, Excel.CurrentWorkbook() is used to retrieve data from the current workbook where the data is structured as a 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 – Retrieving Data from a Local Excel Workbook

In this scenario, Power Query is used to retrieve data from any Excel file stored on a locally accessible disk using the file’s path. The query can refer to the same file that it is stored within, i.e., the referenced Excel file does not need to be an entirely different file. This scenario uses the File.Contents() function which returns the contents of the specified file as binary.

NOTE: If the file’s name or path change, then the query will need to be revised.

To obtain the file path, go to the File ribbon and select Info. Click the Copy path button to copy the file path and file name to the clipboard.

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: Table Data

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 / Range Data

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"

Scenario 3 – Retrieving Data from a SharePoint / Web-based Excel Workbook

In this scenario, Power Query is used to retrieve data from any Excel file stored on a SharePoint or web-based address. The query can refer to the same file that it is stored within, i.e., the referenced Excel file does not need to be an entirely different file. This scenario uses the Web.Contents() function which returns the contents downloaded from the specified URL as binary.

NOTE: If the file’s name or address change, then the query will need to be revised.

To obtain the file path, go to the File ribbon and select Info. Click the Copy path button to copy the file path and file name to the clipboard.

Method 1: Worksheet Data

let
    Source = Excel.Workbook(Web.Contents("https://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: Table Data

let
    Source = Excel.Workbook(Web.Contents("https://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 / Range Data

let
    Source = Excel.Workbook(Web.Contents("https://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"