Power Query Problems

How to Import Data from One Excel Workbook to Another using Power Query

Emma Brown Oct 1, 2026 868 views

Question details

The user needs an efficient way to automate the transfer and formatting of data from weekly report workbooks into a main training workbook without relying on manual entry.

How to Import Data from One Excel Workbook to Another with Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating weekly report data updates and centralizing data across multiple workbooks.
Observed behavior: Currently, the user is spending significant time manually editing, formatting, and copying data between different Excel files.

Before you start

Ensure both your source weekly report and destination training workbook are saved in an accessible local folder or cloud drive. For a more stable connection, format the data in your source workbook as an Excel Table before importing.

Solution 1Recommended

Use Power Query to Automate Data Import

Connect your destination workbook to the external source file using Power Query, allowing you to fetch, transform, and load data automatically.

Power Query creates a live connection to your source workbook. Instead of repeatedly copying and pasting data every week, you can build a one-time connection that retrieves and formats the data automatically with a single click.

Open the Destination Workbook

Launch Microsoft Excel and open the training workbook (or destination file) where you want the new data to be loaded.

Initialize Get Data

Navigate to the Data tab on the ribbon. Click on Get Data, hover over From File, and select From Workbook from the dropdown menu.

Select the Source File

In the file explorer window that appears, locate and select your weekly report file, then click Import.

Choose the Data to Import

The Navigator window will open. Select the specific sheet or table containing your data. If the data needs cleaning (like removing columns), click Transform Data. If it is ready to use, click Load to import it directly into your worksheet.

Refresh Data Weekly

When a new weekly report is ready, simply overwrite the old source file with the new one (keeping the same name and location), then go to the Data tab in your destination workbook and click Refresh All.

Automation Tip: Any formatting or cleaning steps applied during the 'Transform Data' phase are saved. Every time you hit Refresh, Power Query will automatically re-apply those exact same steps to the new data.

Effortless Data Management

Manage and Consolidate Data Efficiently with WPS Office

WPS Spreadsheets provides a highly compatible, lightweight, and user-friendly environment for managing large datasets. You can seamlessly import external data and utilize robust data processing tools without complicated setups.

1. Open WPS Spreadsheets: Launch WPS Office and open your main destination workbook.
2. Access the Import Tool: Navigate to the Data tab on the top ribbon and click on Import Data to start connecting to external sources.
3. Select Source Data: Choose the external file you wish to pull data from and follow the intuitive import wizard to specify your data range and formatting parameters.
4. Analyze and Organize: Use WPS Office's built-in Data tools, such as the Consolidate feature or PivotTables, to automatically organize and summarize the imported weekly reports.

Easily import and consolidate data from external text and data files.Fully compatible with Microsoft Excel (.xlsx) formats and formulas.Free and lightweight alternative for daily data processing and reporting.Built-in smart tools for advanced filtering, PivotTables, and data consolidation.

microsoft office alternative - wps office

Frequently Asked Questions

Will my Power Query connection break if I move the source Excel file?

Yes, Power Query relies on absolute file paths by default. If you move or rename the source workbook, the query will fail to refresh. You can fix this by going to Data > Get Data > Data Source Settings and updating the file path.

Can I import data from multiple workbooks at once using Power Query?

Yes. Instead of selecting 'From Workbook', choose Data > Get Data > From File > From Folder. This allows you to combine multiple weekly reports stored in a single folder into one continuous dataset.

Why is the 'Get Data' option missing or grayed out in my Excel?

The Power Query 'Get Data' feature is built into Excel for Windows (2016 and newer) and Microsoft 365. If you are using an older version (like 2010 or 2013), you must download it as a separate add-in. It may also be limited if you are using certain older versions of Excel for Mac.

Excel & Data Problems