How to Import Multiple OneDrive Workbooks into SharePoint with Excel Power Query
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.
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.
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.
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.
In the Power Query Editor, click Combine > Combine Files, select a sample workbook, and choose the worksheet or object that represents the data layout.
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.
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.
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.
Automate SharePoint list synchronization with Power Automate
Use this after the Power Query output table is available, so new or refreshed rows are added to the SharePoint list automatically.
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. 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. 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. 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.

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.




