Data Import & Export

How to Import Multiple OneDrive Workbooks into SharePoint with Excel Power Query

Jason MillerJason Miller Nov 13, 2026 868 views

Question details

The user needs to consolidate selected columns from multiple date-based Excel workbooks stored in OneDrive into a SharePoint list, despite merged cells, multiple worksheets, and no Excel tables in the source files.

Product
Microsoft Excel, OneDrive, and SharePoint
Device & OS
not provided
Scenario
Workbooks with date-based filenames are uploaded to OneDrive several times a week; selected columns from multiple worksheets must be transferred to a SharePoint list.
Observed behavior
The source workbooks contain merged cells and no Excel tables, so the data is not in a consistent structured format that SharePoint can import directly.
Before you start

Confirm that you have access to the OneDrive folder, the target SharePoint list, and Power Query in Excel plus Power Automate if you plan to automate synchronization. Make a list of the exact columns to extract from each worksheet before building the query.

Solution 1Recommended

Combine and clean OneDrive workbooks with Power Query

Use this when the source workbooks are saved in OneDrive and you need to combine multiple files, handle merged cells, and select only the required columns before loading the result into a structured Excel table.

Power Query connects to the entire OneDrive folder, so any new date-based workbook placed in that folder can be picked up when the query is refreshed.

1
Open Power Query from Excel

In Excel, go to Data > Get Data > From File > From Folder. Enter or browse to the OneDrive folder that contains the date-based workbooks, then select Transform data.

2
Combine the workbook files

In the Power Query Editor, click Combine > Combine Files, select a sample workbook, and choose the worksheet or object that represents the data layout.

3
Promote and clean headers

Click Use First Row as Headers to promote headers. Then select the merged-cell columns, use Transform > Fill > Down or Unpivot Columns to convert the merged layout into a tabular structure.

4
Select the required columns

Hold Ctrl and select only the columns you need, right-click and choose Remove Other Columns, then rename columns to match the SharePoint list fields.

5
Load to a structured table

Click Close & Load To, choose Table, and load the cleaned result into a new Excel worksheet. Refresh the query after new OneDrive files are added.

Cleaned data ready: The query output is now a regular Excel table that can be used as the source for SharePoint synchronization.
Free Microsoft Office alternative

Use WPS Office to Prepare and Edit Excel Data Before OneDrive Upload

For teams that need a free, lightweight spreadsheet editor, WPS Office opens and saves .xlsx workbooks with a familiar ribbon interface, making it easy to clean data, remove merged cells, and prepare structured Excel tables before uploading them to OneDrive for SharePoint import.

  1. 1. Download and install WPS Office: Go to the WPS Office download page, install the suite, and open the Excel workbook you need to clean.
  2. 2. Edit and structure the workbook: Use WPS Spreadsheet to unmerge cells, create Excel tables, rename columns, and remove unnecessary sheets before saving the file as .xlsx.
  3. 3. Upload to OneDrive and continue the import: Upload the cleaned workbook to the OneDrive folder used by Power Query, then return to Excel and refresh the query.
Free to use for everyday spreadsheet editing and data cleanupFull compatibility with Microsoft Excel .xlsx, .xls, and CSV formatsFamiliar ribbon interface for a smooth Microsoft Office migrationLightweight installation that runs well on lower-spec devicesSupports multiple worksheets and merged-cell layouts when preparing source data
microsoft office alternative - wps office

Frequently Asked Questions

Can I import OneDrive workbooks into SharePoint without Power Query?

If the files are already simple Excel tables, you can export each file as CSV or use a SharePoint import option. However, for multiple workbooks with merged cells and multiple worksheets, Power Query is much more reliable.

How do I handle merged cells in Power Query?

After promoting headers, select the merged-cell columns and use Transform > Fill > Down to repeat the missing values, or use Unpivot Columns to turn merged labels into a normalized table layout.

Will this process automatically pick up new workbooks uploaded to OneDrive?

Yes, when you create a folder-based Power Query connection, refreshing the query can combine new date-based workbooks added to that OneDrive folder. Power Automate can then sync the newly loaded rows to SharePoint.

Does WPS Office support Power Query and Power Automate?

No, Power Query folder combination and Power Automate SharePoint flows are Microsoft 365 features. WPS Office can still be used to open, clean, and save .xlsx source files before you upload them to OneDrive.

What is the best way to avoid merge issues in the future?

Redesign each source workbook as an Excel table with one header row and no merged cells. This makes the Power Query transformation and SharePoint column mapping much simpler.