Managing sales data from multiple Excel files can be challenging when each file has different column names and formats.
In this example, Power Query automatically imports every Excel file from a folder and converts them into a standardized dataset.
The automation process includes:
- Importing multiple Excel files from a folder
- Ignoring hidden and temporary files
- Detecting tables or worksheets automatically
- Standardizing different column names
- Handling missing columns
- Converting data types
- Calculating sales amount and net amount
- Creating a Year-Month column for reporting
The final result is a clean sales dataset that can be used directly in Excel, Power BI, or other reporting tools.
This method is useful for sales reporting, ETL workflows, data consolidation, and business automation.Managing sales data from multiple Excel files can be challenging when each file has different column names and formats.
In this example, Power Query automatically imports every Excel file from a folder and converts them into a standardized dataset.
The automation process includes:
- Importing multiple Excel files from a folder
- Ignoring hidden and temporary files
- Detecting tables or worksheets automatically
- Standardizing different column names
- Handling missing columns
- Converting data types
- Calculating sales amount and net amount
- Creating a Year-Month column for reporting
The final result is a clean sales dataset that can be used directly in Excel, Power BI, or other reporting tools.
This method is useful for sales reporting, ETL workflows, data consolidation, and business automation.
videolink - https://youtu.be/qvYNIsT5_JM
Download the sample file below.
You can download the zip file in [Download] page
The file name is 51.PQ_Folder_Combine_Template.zip
Use the English M code provided for your dataset.
You may need to manipulate the M code under your circumstance. You consider this point and use M code
below txt file is detailed-explanation. You use M code in reference with this explanation.
---------------------------------------------------------
Need a custom automation for your Excel workflow?
I can build a tailored solution based on your data.
✔ Used in real-world business automation
✔ Saves hours of manual work
minbedata@gmail.com
You can pay via PayPal.
You don't worry about conversing with me.
My Major is an English literature, so I can speak English fluently and read, listening, writing proficiently
korean version web address (한국버전) is
<a href="https://blog.naver.com/minbe-data" target="_blank">네이버 블로그 바로가기</a>