'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel.CurrentWorkbook Not Showing Tables in Power Query
Question details
The user's Power Query is failing to load existing Excel tables because the Excel.CurrentWorkbook function returns no results.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to retrieve data from an existing Excel table using the Excel.CurrentWorkbook() function in Power Query.
- Observed behavior
- The query returns empty results or no tables, typically because the table is stored in a different workbook than the query itself.
Before troubleshooting, verify the exact file name and location of both the workbook containing your target data table and the workbook where you are building the query.
Consolidate the Query and Table into the Same Workbook
The Excel.CurrentWorkbook function strictly references the workbook where the query itself is stored. To fix the issue, the table and query must reside in the exact same file.
When you use Excel.CurrentWorkbook(), Power Query looks for tables and named ranges within the specific file hosting the query. If your source data table is in 'Data.xlsx' but your query is being created in 'Report.xlsx', the function will not find the table and will return an empty result.
Open the workbook where your target Excel table is located. Ensure the data is actually formatted as a Table (Insert > Table) and note the file name.
Identify the workbook where you are currently writing or executing the Power Query.
Either copy your source data table into a new sheet within the query's workbook, or recreate your Power Query inside the workbook that currently holds the data table.
Once the table and query share the same workbook, open the Power Query Editor, click 'Refresh Preview', and your table will now appear in the Excel.CurrentWorkbook results.
Process Data Seamlessly with WPS Office
If you are struggling with complex Power Query functions and cross-workbook data referencing in Microsoft Excel, consider switching to WPS Office. It offers a lightweight, highly compatible spreadsheet application to manage, analyze, and visualize your data effortlessly without the steep learning curve.
- 1. Download and Install: Get the free WPS Office suite from the official website and install it on your device.
- 2. Open Multiple Workbooks: Launch WPS Spreadsheet and easily open multiple .xlsx files in a convenient tabbed interface.
- 3. Reference Data Easily: Use standard formulas like VLOOKUP or cross-workbook referencing to combine your data effortlessly.
Frequently Asked Questions
How do I query data from a different Excel workbook?
Instead of using Excel.CurrentWorkbook(), use the 'Get Data > From File > From Workbook' feature in Excel. This generates the Excel.Workbook(File.Contents("filepath")) function to safely extract tables from external files.
Why does my named range not appear in Excel.CurrentWorkbook?
Excel.CurrentWorkbook will only list named ranges and tables that exist in the exact workbook where the query is saved. Verify that your named range is defined in the current file, not an external one.
Does Excel.CurrentWorkbook update automatically when I add new tables?
Yes, if the new tables are created within the same workbook as the query, they will be included in the Excel.CurrentWorkbook() output upon refreshing the query.
