Summary of "How to Append Multiple Excel Files in One Sheet | Power Query Tutorial in Hindi"

How to append multiple Excel/CSV files into one sheet using Power Query (Hindi tutorial)

Overview

Problem: You have many Excel or CSV files in a folder (for example, monthly bank or sales files) with the same columns and you want to combine (append) them vertically into one table without manual copy/paste.
Solution: Use Excel’s Power Query (Data → Get Data → From File → From Folder → Transform Data) to automatically extract, combine and load all files in the folder. Once set up, add files to the folder and Refresh to update the combined table.

Step-by-step method (detailed)

Prepare files
- Ensure all source files have identical column headers and column order.
- Put all files to be combined into a single folder (avoid unrelated files in that folder).
Start Power Query
- Excel → Data tab → Get Data → From File → From Folder.
- Select the folder containing the files.
- Click Transform Data (don’t click Combine & Load immediately if you need to transform).
Keep only the Content column
- In the query preview, remove other columns (right-click the Content column → Remove Other Columns). The Content column contains file binaries.
Extract workbook content
- Add Column → Custom Column.
- Name it (e.g., Combine) and use the formula: Excel.Workbook([Content])
- Click OK. This converts each file binary to a record/table (returns fields such as Name, Data, Item, Kind, Hidden).
Remove the original Content column
- Delete the Content column (right-click → Remove).
Expand the workbook list
- Click the double-arrow expand icon on the new Combine column.
- In the expand dialog, select the fields you need (commonly Name and Data). Uncheck “Use original column name as prefix” to avoid extra prefixes.
- Click OK.
Expand the Data column to actual table columns
- Click the expand icon next to the Data column (this shows actual table columns from each file).
- Select the data columns you want (e.g., 9 columns). Click OK.
- Rows from all files will be stacked.
Fix header/duplicate header rows
- If header rows from each file appear as data rows:
  - Use Home → Use First Row as Headers if needed.
  - Remove repeated header rows by filtering: pick a column that contains the header text (e.g., CustomerName = “CustomerName”) and uncheck that value to remove those rows.
  - Alternatively, filter/remove rows where all columns equal header values.
Correct data types
- Select all columns (Ctrl+A) → Transform → Detect Data Type (Power Query scans up to ~1000 rows and assigns types).
- Manually adjust any column data type if needed.
Load results
- Home → Close & Load to load into Excel as a table (if within Excel row limits).
- Or Home → Close & Load To… to choose Connection only, PivotTable report, Data Model, or other destinations (useful for very large datasets).
Refresh when new files are added
- Add/copy new files to the same folder (keep naming/structure consistent), then right-click the query/table in Excel and choose Refresh — Power Query will append new files automatically.

Key options & tips

The M function used to read Excel binary is: Excel.Workbook([Content])
When expanding the workbook output, available columns include: Name, Data, Item, Kind, Hidden. Typically expand Data (and maybe Name).
Uncheck “Use original column name as prefix” to avoid long prefixed column names.
Power Query has no Ctrl+Z (Undo); remove or step back by deleting steps in the Applied Steps pane.
Choose Close & Load vs Close & Load To based on size and intended use (table vs Pivot vs connection only).

Common mistakes & best practices (checklist)

Column names must match exactly across files (same spelling). Case differences are usually fine but can cause problems in some cases.
Column order should be identical across files.
Remove merged headers, merged cells, extra title rows or subtitles — there should be a single header row per file.
Remove extra blank rows and formatting; convert source ranges into proper Excel tables before saving.
Remove invisible trailing spaces in headers and cells (these break matches).
Ensure consistent data types (dates as dates, numbers as numbers, text as text); use Detect Data Type to help.
Keep only expected Excel/CSV files in the folder (no unrelated files like PDFs or system files).
Keep file and sheet names consistent and simple.
Keep the folder location stable — moving/changing the folder breaks the query connection.
If a file has missing columns, “pad” missing columns with default values (null/0/placeholder) before appending (normalization).
For very large or advanced scenarios consider VBA or Python (pandas); for most users Power Query is recommended.

Alternatives to Power Query

Manual copy-paste (not recommended — slow and error-prone).
VBA/Macros (for advanced Excel users).
Python with pandas (for data professionals).

Troubleshooting notes

Repeating headers after appending: remove header rows imported as data via filtering, then re-apply Use First Row as Headers.
Wrong data types (e.g., dates treated as text): use Transform → Detect Data Type or set types manually.
Files with tables instead of sheets or multiple sheets: filter by Kind (Sheet/Table) or Item to pick the correct source.