How to get data from another Excel workbook with Power Query
How-to · By Tahir Nas · Examples run in our simulator · Updated
10 min read
To get data from another Excel workbook, go to Data > Get Data, choose the Excel Workbook connector, pick the file, then pick a table or sheet in the Navigator and select Transform Data. Power Query records the file path and the item you chose, so the next refresh reads the same place again without any copying or pasting.
The steps in Excel
Microsoft's Excel connector documentation describes the same three steps in every Power Query desktop host:
- On the Data ribbon, select Get Data and choose the Excel Workbook connector. In Excel for Windows it sits under From File. Microsoft's page on where to get data gives the same starting point for Excel for Windows and Excel for Mac. The post on Power Query in Excel for Mac covers what else differs.
- Browse to the workbook and select Open.
- In the Navigator, select the table or sheet you want, then select Load or Transform Data.
If this is your first query, your first Power Query in Excel walks through the whole journey from import to refresh. Pick Transform Data unless the source is already perfect. Loading straight away skips the editor, and you will usually want to set types, remove columns or filter rows before the data reaches a sheet.
What Power Query writes for you
Open the Advanced Editor after you pick a table and you will see two steps. The first reads the file, the second picks one item out of it:
let
Source = Excel.Workbook(File.Contents("C:\Reports\Sales.xlsx"), null, true),
Sales_Table = Source{[Item = "Sales", Kind = "Table"]}[Data]
in
Sales_TableFile.Contents returns the file as binary. Excel.Workbook turns that binary into a table with one row per sheet, table and defined name, and columns Name, Data, Item, Kind and Hidden. The third argument, true, is delayTypes: it leaves columns untyped so that a later Changed Type step sets them.
The second step is a lookup. Source{[Item = "Sales", Kind = "Table"]} finds the one row whose Item is Sales and whose Kind is Table, and [Data] takes the nested table from that row. You can rebuild the same shape with #table to see how the lookup behaves:
let
SalesTable = #table(type table [Region = text, Month = text, Units = number], {{"North", "2026-09", 120}, {"South", "2026-09", 95}}),
Sheet = #table({"Column1", "Column2", "Column3"}, {{"Monthly sales export", null, null}, {null, null, null}, {"Region", "Month", "Units"}, {"North", "2026-09", 120}, {"South", "2026-09", 95}}),
Source = #table(
type table [Name = text, Data = table, Item = text, Kind = text, Hidden = logical],
{
{"Sales", SalesTable, "Sales", "Table", false},
{"Export", Sheet, "Export", "Sheet", false}
}
),
Sales = Source{[Item = "Sales", Kind = "Table"]}[Data]
in
SalesResult
| Region | Month | Units |
|---|---|---|
| North | 2026-09 | 120 |
| South | 2026-09 | 95 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The result is the two-row Sales table with Region, Month and Units already as headers. The lesson on importing from Excel tables, sheets and the navigation table walks through this structure step by step.
Table, sheet or named range: which to pick
The Navigator lists three kinds of item, and they do not behave the same on refresh.
| Item | What you get | Use it when |
|---|---|---|
Table (Kind = "Table") | The Excel table's own headers and rows, sized to the table | The source owner formats the data as a table. This is the best case. |
Sheet (Kind = "Sheet") | Every used cell, with blanks as null and headers in a row | The data is not in a table and you cannot change the source. |
Defined name (Kind = "DefinedName") | The cells inside a named range | Someone maintains a named range for exactly this purpose. |
A table is the safest choice because its name stays the same unless someone renames it, and it expands when rows are added. If you own the source workbook, convert the range to a table and give it a clear name before you connect.
When a sheet holds more than one block of data, the Navigator can also offer suggested tables. Microsoft notes that if the sheet changes enough, a suggested table may not refresh properly, so treat those as a convenience rather than a stable source.
Cleaning a sheet
A sheet arrives with generic Column1, Column2 names and whatever sits above the real header row. Remove the banner rows first, then promote headers, then set types:
let
Sheet = #table({"Column1", "Column2", "Column3"}, {{"Monthly sales export", null, null}, {null, null, null}, {"Region", "Month", "Units"}, {"North", "2026-09", 120}, {"South", "2026-09", 95}}),
Skipped = Table.Skip(Sheet, 2),
Promoted = Table.PromoteHeaders(Skipped, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {{"Region", type text}, {"Month", type text}, {"Units", Int64.Type}})
in
TypedResult
| Region | Month | Units |
|---|---|---|
| North | 2026-09 | 120 |
| South | 2026-09 | 95 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The output has the headers Region, Month and Units and the two data rows. Table.Skip drops a fixed number of rows, and Table.PromoteHeaders turns the first remaining row into column names. If the number of banner rows varies, the lesson on removing report banners before promoting headers shows how to skip until a known header appears.
Excel.Workbook also has a useHeaders option, but Microsoft's reference page warns that it converts numbers and dates to text using the current culture, so the same query can behave differently on another computer. Its recommendation is to promote headers with Table.PromoteHeaders instead, which is what the block above does.
Keep the query working when the source changes
A query from another workbook depends on two things outside your control: the file's location and the names inside it. The usual refresh failures trace back to one of them. Renamed columns inside the table are a separate problem, covered in handling changing column headers.
Someone renames the table
The lookup step matches on the name. If the source owner renames Sales to Sales_2026, the lookup finds no row and the refresh fails:
let
Source = #table(type table [Name = text, Data = table, Item = text, Kind = text, Hidden = logical], {{"Sales_2026", #table({"Region", "Units"}, {{"North", 120}}), "Sales_2026", "Table", false}}),
Sales = Source{[Item = "Sales", Kind = "Table"]}[Data]
in
SalesResult: an error
Expression.Error: The key didn't match any rows in the table.
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
The simulator raises Expression.Error: The key didn't match any rows in the table. That is the message you will see in Excel too, and the key didn't match any rows error page covers other causes.
The best fix is agreeing a stable name with the person who owns the file. If the name genuinely changes each period, filter the navigation table by a rule instead of matching one exact name:
let
Source = #table(
type table [Name = text, Data = table, Item = text, Kind = text, Hidden = logical],
{
{"Sales_2026", #table({"Region", "Units"}, {{"North", 120}}), "Sales_2026", "Table", false},
{"Lookup", #table({"Code"}, {{"A"}}), "Lookup", "Sheet", true},
{"Export", #table({"Column1"}, {{"x"}}), "Export", "Sheet", false}
}
),
SalesTables = Table.SelectRows(Source, each [Kind] = "Table" and Text.StartsWith([Name], "Sales")),
Sales = if Table.RowCount(SalesTables) = 1 then SalesTables{0}[Data] else error "Expected exactly one Sales table, found " & Text.From(Table.RowCount(SalesTables))
in
SalesResult
| Region | Units |
|---|---|
| North | 120 |
Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.
This returns the one-row table from Sales_2026. The if guard matters: if the workbook ever holds both Sales_2025 and Sales_2026, the query stops with a clear message instead of quietly loading whichever table happens to come first. Avoid picking by position alone (Source{0}[Data]): adding a sheet or table to the source workbook can change which row comes first, and the query would then load the wrong data without any error.
The Hidden column helps in the same way. A filter such as Table.SelectRows(Source, each not [Hidden]) drops hidden helper sheets before you choose.
The file moves or is renamed
The path is written into the Source step as text. When the file moves, edit that step in the Advanced Editor, or store the path somewhere one change updates every query. A Power Query parameter does this, and the lesson on parameters: one value, many queries explains the idea. You can also keep the path in a cell of the reporting workbook, which the post on using an Excel cell as a Power Query parameter sets up. Reading a cell and a file in the same step can trigger a Formula.Firewall error, so keep the cell lookup in its own query and see the lesson on privacy levels and the formula firewall if it appears.
If the workbook lives on a network share and the report will be refreshed in the Power BI service, Microsoft's connector page advises using the fully qualified server name (for example \\ServerName.domain.com\FolderName\File.xlsx) rather than a short server name, because the short name can fail after publishing.
Rows are missing or the load is slow
Microsoft's connector page explains that Power Query reads the sheet dimensions stored in the file. If a tool wrote those dimensions wrongly, some rows or columns are not loaded; if they are far too large, the load is slow. Opening and resaving the workbook in Excel rewrites them. When you cannot do that, ask Excel.Workbook to work out the used area itself:
let
Source = Excel.Workbook(File.Contents("C:\Reports\Export.xlsx"), [DelayTypes = true, InferSheetDimensions = true]),
Export_Sheet = Source{[Item = "Export", Kind = "Sheet"]}[Data]
in
Export_SheetInferSheetDimensions works for Open XML files such as .xlsx, not for legacy formats.
Older formats, online files and many workbooks
A few situations need a different route:
- .xls and .xlsb files are read through the Access Database Engine (ACE) provider, which must be installed. Microsoft lists side effects: values can arrive as formatted text, and a column whose first eight rows look numeric can return
nullfor later text values. - Workbooks on SharePoint or OneDrive: Microsoft's guidance is to copy the path from File > Info > Copy Path in Excel desktop, remove
?web=1from the end, and use the Web connector. - CSV files open in Excel but are not Excel files. Use the Text/CSV connector.
- A folder of workbooks with the same layout is a job for the Folder connector. The guide to combining monthly files into one table covers it end to end. The post on replacing a monthly copy-paste process builds the same thing from a folder of CSV files.
Practise loading tables from a workbook
Two exercises use the same navigation table you saw above. Load the named table from an Excel workbook practises picking the right item, and append every monthly table in a workbook practises filtering the navigation table and combining what is left. Both run in the browser on Power Query Exercises, with the result checked against an expected table.
Frequently asked questions
Does the source workbook need to be closed?
For .xlsx files, Power Query reads the file through File.Contents, so it sees what was last saved, not unsaved edits in an open window. Save the source before you refresh. For legacy .xls files, Microsoft notes that having the workbook open while importing can make values come back in a different format, so close it.
Why did my numbers change slightly after import?
Excel stores numbers in binary floating point, and some decimals cannot be stored exactly. The numeric precision note in Microsoft's connector documentation gives the example of 0.049, which is stored in the file as 0.049000000000000002. Power Query shows the stored value; Excel shows a rounded one. Round in Power Query, or use a fixed decimal type, if the extra digits matter.
Can I get data from the workbook I am working in?
Yes, but with a different function. Excel.CurrentWorkbook returns the tables, named ranges and dynamic arrays in the current workbook. Unlike Excel.Workbook, it does not return sheets.
Sources
- Excel connector documentation (learn.microsoft.com)
- where to get data (learn.microsoft.com)
- File.Contents (learn.microsoft.com)
- Excel.Workbook (learn.microsoft.com)
- numeric precision note in Microsoft's connector documentation (learn.microsoft.com)
- Excel.CurrentWorkbook (learn.microsoft.com)
About the author
Tahir Nas, software engineer, writes and reviews the lessons, guides and exercises on Power Query Exercises. Tahir has 8 years of experience in tech, working across system administration, cloud architecture, QA, scripting and automation, and software engineering in many languages and mainstream databases. GitHub profile. Every M example shown with a result was run in the site's simulator when the page was built, and the output is that run's result; examples marked for Excel and Power BI only are shown without output. How content is tested.
Keep reading
- How to refresh Power Query automatically in ExcelRefresh Power Query when an Excel workbook opens, every few minutes or on a timer with VBA, and make the queries safe to refresh when nobody is watching.
- Use an Excel cell as a Power Query parameterRead a date, number, text or file path from a named Excel cell into Power Query, use it to filter a query, and avoid the Formula.Firewall error.
- CSV quirks in Power Query: line breaks, quotes and delimiters inside valuesFix CSV imports that split rows or shift columns: quoted line breaks, commas inside values, doubled quotes, semicolon files and the fixed column count.
Practise Power Query in the browser with graded exercises checked against fresh data.
Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.