Power Query Problems

How to Automatically Consolidate Regional Excel Files from SharePoint

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to automatically combine and consolidate recurring regional expense files from SharePoint into a single workbook without manually copying and pasting data.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating recurring regional files (APAC, EMEA, NEA, Americas) with identical layouts stored in quarterly SharePoint folders.
Observed behavior: The goal is to create a central consolidation file that automatically aggregates and totals the values from multiple regional SharePoint files while either retaining its layout or using a PivotTable.

Before you start

Ensure you have the correct SharePoint folder URL and possess the necessary user permissions to access all regional expense files stored within it.

Solution 1Recommended

Use Power Query to Import and Combine SharePoint Files

The most efficient and scalable way to automate the combination of identical regional files is by using Power Query to connect directly to the SharePoint folder.

Power Query allows you to fetch all files from a specific SharePoint directory automatically. When identical files are added in the future, a simple refresh will update your combined data.

Connect to SharePoint Folder

Open a new Excel workbook, go to the Data tab, select 'Get Data', choose 'From File', and then click 'From SharePoint Folder'.

Enter the Folder URL

Paste your SharePoint site URL into the prompt and click OK. Once the file list appears, click 'Transform Data' to open the Power Query Editor.

Combine the Files

Filter the folder list to ensure only your regional expense files are shown. Click the double-down-arrow icon ('Combine Files') in the Content column header.

Unpivot Data (Optional but Recommended)

If your months or dates are spread across multiple columns, select them, right-click, and choose 'Unpivot Columns' to turn them into rows for easier analysis.

Load the Data

Once the queries are appended, click 'Close & Load' to return the combined data into a worksheet or add it to the Data Model.

Pro Tip: Make sure all regional files share the exact same column names and structures, otherwise Power Query may struggle to append them correctly.

Solution 2

Summarize Consolidated Data with a PivotTable

After combining your SharePoint data via Power Query, use a PivotTable to dynamically total and analyze the regional expenses.

Solution 3

Use External Links and SUM Formulas

If you want to maintain a strict, pre-existing layout without using Power Query, you can link individual regional worksheets and consolidate them using formulas.

Free Microsoft Office alternative

Experience Seamless Data Consolidation with WPS Office

While advanced SharePoint Power Query connections are native to Microsoft Excel, WPS Office provides a highly capable, free, and lightweight alternative for standard data analysis. Enjoy powerful built-in tools, including Data Consolidation and advanced PivotTables, without the heavy subscription fees.

1. Download and Install: Download WPS Office Free from the official website and follow the quick installation process.
2. Open Your Spreadsheets: Seamlessly open your existing .xlsx expense files without any formatting issues or data loss.
3. Consolidate Your Data: Navigate to the Data tab to effortlessly consolidate multiple regional worksheets into one comprehensive summary.

Free, lightweight, and easy-to-use alternative to Microsoft Office.100% format compatibility with Microsoft Excel (.xlsx and .xls) files.Powerful built-in Data Consolidation tools for merging multiple worksheets.Advanced PivotTable capabilities for effortless data summarization.

microsoft office alternative - wps office

Frequently Asked Questions

Will Power Query update automatically when a new regional file is added to SharePoint?

Yes. When you use the 'From SharePoint Folder' connector in Power Query, it targets the folder rather than specific files. Any new identical files added to that folder will automatically be included the next time you click 'Refresh All'.

Can I combine files if they have slightly different column headers?

While Power Query can combine files with different headers, it will create separate columns for mismatched names, resulting in null values for the files missing those headers. It is highly recommended to standardize your column headers across all regional files before importing.

Why should I unpivot my date columns in Power Query?

Unpivoting transforms wide data (where every month is a new column) into a tabular format (where months become a single 'Attribute' column). This ensures your PivotTable scales dynamically and doesn't break when a new month is added to the source data.

Excel & Data Problems