How do I merged 2 excel files from a folder?

Anonymous
2025-09-10T18:01:34.2133333+00:00

@Hendrix-C

Power Query merging 2 files from a folder

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sergei 0 Reputation points
    2026-10-17T18:25:21.4233333+00:00

    The error comes from the sample-file step: Power Query remembered the exact sheet name from the first file (“Historical Metrics Report - 2025” or similar), and the other file’s sheet has a different name.

    Fix: in the Power Query editor, open the query under “Transform Sample File”, select the Navigation step and look at the formula bar. It will be something like = Source{[Item="Historical Metrics Report - 2025",Kind="Sheet"]}[Data] Change it to take the first sheet by position instead of by name: = Source{0}[Data] Then Close & Load. Every file in the folder is now read from its first sheet, whatever its name.

    Was this answer helpful?

    0 comments
  2. Ashish Mathur 102.6K Reputation points Volunteer Moderator
    2025-09-11T00:24:14.21+00:00

    Hi,

    Watch the video in this link - https://www.youtube.com/watch?v=yL11ugShdrk

    Was this answer helpful?

    0 comments
  3. Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator
    2025-09-10T21:10:44.72+00:00

    Hi @Anonymous,
    Thanks for your sharing.

    About your two first screenshots, they're just getting started steps and Power query just shows you a preview. You just need to click OK to move to the next step.

    About your below two screenshots, the error occurs when Power Query tries to access a sheet or table in Excel file that doesn't exist. From the error message, it's looking for a sheet named "Historical Metrics Report - 202" but one of the files is missing or the sheet is named differently. Power query only combines files with matching structure (same sheet name, same columns) so you need to make sure both the files have the same structure.
    May I ask if you can share me the Excel files so I can take a look and give you a guideline to do it properly since Power Query is a quite advanced method. I just sent you a private message so you can also share it with me in there.

    Please feel free to ask if there's anything else I can help. Hope to hear from you soon.

    Was this answer helpful?

    0 comments
  4. Hendrix-C 20,015 Reputation points Microsoft External Staff Moderator
    2025-09-10T18:27:59.98+00:00

    Hi @Anonymous,

    Thank you for reaching out to Microsoft Q&A Community.
    To merge two files from a folder using Power Query, you can follow these steps:
    1-Load files from folder:

    • Go to Data tab > Get Data > From File > From Folder
      User's image
    • Browse to the folder containing your files and click OK
    • Click Combine > Combine and transform data.

    2-Combine files

    • Power query editor will automatically open one file and use as a sample.
    • You can adjust the transformation steps (remove rows, headers, etc.) if needed.

    3-Finalize and load

    • Click close & load to load the merged data into Excel.

    This is a basic guideline to use Power Query to merge two files in Excel. Please share me more details about your situation and your request so I can provide more detailed and appropriate working steps to help you.

    Hope to hear from you soon.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread 

    Was this answer helpful?