Combine Excel Test Results from Multiple Sheets with Power Query
Question details
The user needs to combine student test results from multiple Excel sheets or files into one summary containing the current test, achievement date, and percentage score.
- Product
- Microsoft Excel Power Query and Power BI
- Device & OS
- not provided
- Scenario
- Each test result is stored in a separate Excel sheet, workbook, table, or source file, and the combined data will be used in Excel or a Power BI dashboard.
- Observed behavior
- The desired result is a single refreshable summary table that combines all test results and can provide data to Power BI.
Make sure every source table uses consistent column names and data types, such as Student, Test, Achievement Date, and Percentage Score. Store source workbooks in a consistent folder or supported SharePoint location if you plan to refresh the query automatically.
Append consistent Excel test-result tables with Power Query
Use this approach when each sheet or file contains the same type of test-result columns and you need one refreshable summary table.
Power Query appends rows from multiple tables or files when their column structures are consistent. You can then select only the required fields, clean the data, and load the result into Excel or Power BI.
Open each source workbook in Excel, select the test-result range, choose Insert > Table, and confirm that the table has headers. Use matching column names and compatible data types in every table.
In Excel, select Data > Get Data and choose the appropriate source, such as From Workbook, From Folder, or From SharePoint Folder. Select the workbook, folder, or location containing the test-result tables.
In the Navigator or Power Query Editor, select the relevant tables and choose Combine > Append Queries or Append Queries as New. Select Three or more tables when necessary, then add all test-result tables.
In Power Query Editor, use Home > Choose Columns to retain the current test, achievement date, and percentage score. Set each column's data type by selecting the column and choosing the correct type from Transform > Data Type.
Select Home > Close & Load to place the summary in Excel, or choose Home > Close & Load To and select a data model or connection for use in Power BI.
Connect the combined query to a Power BI dashboard
Use this workflow when the final goal is an interactive dashboard showing student achievement across tests and dates.
Use WPS Office as a Lightweight Alternative for Managing Excel Test-Result Workbooks
WPS Office provides a free, lightweight spreadsheet environment with a familiar interface and strong Microsoft Excel format compatibility. It can help you review, organize, and maintain test-result workbooks while you migrate existing files, although Microsoft Power Query and Power BI refresh features remain Microsoft services.
Frequently Asked Questions
How should Excel sheets be structured before combining them with Power Query?
Give each source the same column headers, such as Student, Test, Achievement Date, and Percentage Score. Convert each range to an Excel table with Insert > Table and use consistent data types.
Should I use Append or Merge in Power Query for test results?
Use Append Queries when the sheets contain similar rows that should be stacked into one result. Use Merge Queries only when you need to join related tables using a matching field, such as Student ID.
Can Power Query combine files stored in a folder?
Yes. In Excel, select Data > Get Data > From File > From Folder, choose the folder, and select Combine & Transform Data. The files should use a consistent structure so Power Query can process them together.
Can the combined Excel query feed Power BI?
Yes. You can load the query into Excel for review or use the same transformation logic in Power BI Desktop. After publishing to the Power BI service, configure credentials and scheduled refresh for supported source locations.
