Combine Multiple Excel Files from SharePoint in Power BI
You are connecting to a SharePoint folder containing 100 Excel files. Each file has a similar structure but different column names. What is the best practice to combine these files into a single table while preserving the data?
Quick Answer
Using Power Query's 'Combine Files' feature, working from a sample file, promoting headers, and standardizing column names is the answer because this scenario adds a wrinkle that a simple combine wouldn't handle: the files share a similar structure but not identical column names, so straightforward combination would leave you with a messy table full of inconsistently named or duplicated columns. Combine Files works by taking one representative sample file, letting you build the transformation steps against it -- including promoting the first row to headers and renaming columns to a standard, agreed-upon set -- and then automatically replaying that exact sequence of steps against every other file in the folder. Because the column-name standardization happens as part of the sample transformation, it gets applied consistently to all 100 files rather than requiring you to fix each file's headers individually, which is what makes this approach efficient at that scale while still preserving every file's data. The general lesson here is that Combine Files isn't just for structurally identical files; by doing your cleanup and standardization work inside the sample file step, you can normalize real-world inconsistencies, like inconsistent naming, before the combination happens, so watch for scenarios that mention many files with a similar but not identical structure as a signal that the sample-file transformation step is where the real work needs to happen.
⚠ Common exam trap
Many exam-takers confuse 'Combine Files' (which unions multiple files with a consistent transformation) with 'Merge Queries' (which joins tables horizontally) or 'Append Queries' (which stacks tables but lacks automated column standardization).
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Use Power Query's 'Combine Files' feature, selecting a sample file and promoting headers, then transforming column names to a standard set.
Power Query's 'Combine Files' feature is designed specifically for this scenario: it uses a sample file to infer the transformation logic (e.g., promoting headers), then applies that logic to all files in the folder. By transforming column names to a standard set within the sample file step, you ensure consistent column names across all files, preserving data integrity while combining them into a single table.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use Power Query's 'Combine Files' feature, selecting a sample file and promoting headers, then transforming column names to a standard set.
Why this is correct
This automates combining files with different structures.
- ✗
Load each file as a separate table and create relationships in the model.
Why it's wrong here
This creates multiple tables, not a single combined table.
- ✗
Use Power Query's 'Merge Queries' to join all files into one table.
Why it's wrong here
Merge is for combining columns from different tables, not rows from multiple files.
- ✗
Use 'Append Queries' to stack all files, then rename columns manually.
Why it's wrong here
Append requires identical column names; renaming manually is error-prone.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 217 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on PL-300
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. You are connecting to a SharePoint folder that contains Excel workbooks. Each workbook has multiple sheets. You need to combine data from a specific sheet named 'Sales' across all workbooks. Which Power Query approach should you use?
medium- A.Use the SharePoint Online List connector and select the document library.
- ✓ B.Use the SharePoint folder connector, filter by .xlsx, then expand the Content column and filter by sheet name 'Sales'.
- C.Use the Excel connector and specify the folder path.
- D.Use the Web connector and provide the SharePoint site URL.
Why B: The SharePoint folder connector retrieves all files in the folder, including Excel workbooks. By filtering for .xlsx files and then expanding the Content column, you access the binary data of each workbook. You can then filter by the 'Sales' sheet name to combine data from that specific sheet across all workbooks, which is the only approach that directly handles multiple workbooks with multiple sheets.
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.