How to remove headers when importing from folder

[cheid_4838]

I have a folder containing multiple .CSV files that I had to daily and then refresh when a new file is added.  The problem is that each file contains a header for each column.  I am currently getting an error after importing because I promote the header from one file, but the other headers still exist and that causes conflict with the data types. How can I import the header from one file and then remove the header from others as new files are added?     Thanks.

[v-dineshya · Accepted solution]

Hi @cheid_4838 ,

Thank you for reaching out to the Microsoft Community Forum.

You want to combine all three files into one table with a single header row and no repeated headers from other files.

M code example you can paste directly into Power Query:

let
// Get all files in folder
Source = Folder.Files("C:\YourFolderPath"), // Change this to your folder path

// Keep only CSV files
CsvFiles = Table.SelectRows(Source, each Text.EndsWith([Extension], ".csv")),

// Extract content from each file
AddContent = Table.AddColumn(CsvFiles, "Content", each Csv.Document(File.Contents([Folder Path] & [Name]), [Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None])),

// Remove top row from each file (assumes it's the header)
RemoveTopRow = Table.TransformColumns(AddContent, {"Content", each Table.Skip(_,1)}),

// Combine all tables together
CombineTables = Table.Combine(RemoveTopRow[Content]),

// Use headers from one file (File1.csv in this case)
HeaderFile = Csv.Document(File.Contents("C:\YourFolderPath\File1.csv"), [Delimiter=",", Columns=3, Encoding=65001, QuoteStyle=QuoteStyle.None]),
PromotedHeaders = Table.PromoteHeaders(Table.Combine({HeaderFile, CombineTables}))
in
PromotedHeaders

Note: Folder.Files(...) gets all CSV files. Each file is loaded, and the first row is skipped using Table.Skip. Then the contents of all files are combined into one table. Finally, headers from one known file (e.g., File1.csv) are used to promote column names correctly.

If my response has resolved your query, please mark it as the "Accepted Solution" to assist others. Additionally, a "Kudos" would be appreciated if you found my response helpful.

Thank you

[DataNinja777]

Hi @cheid_4838 ,

When importing multiple CSV files from a folder in Power Query, the issue often arises because each file contains its own header row, and promoting the header from just one file causes conflicts due to repeated headers in others. To fix this, start by going to "Get Data" > "Folder", select your folder, and then click "Transform Data" instead of "Combine". In the resulting query, you’ll see a column named Content. Instead of letting Power Query auto-promote headers, use the following code to manually extract the content without promoting headers:

= Table.AddColumn(Source, "Data", each Csv.Document([Content], [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]))

Expand the Data column so you see each file's rows as individual lists. To remove the headers from all files, you need to assign a row index within each file so you can filter out the first row. To do this, group by the file name (the Name column), add an index inside each group starting from 0, and then filter out where the index equals 0, which represents the header row in each file. Here's a simplified pattern for that step:

= Table.Group(PreviousStep, "Name", {
    {"AllData", each Table.Skip(Table.AddIndexColumn(_, "RowIndex", 0, 1), 1)}
})

Then expand the grouped AllData tables back into one combined table. Now you have all data with headers removed.

To apply correct headers, extract them from one designated file (e.g., the first file alphabetically or a file you explicitly named like "HeaderTemplate.csv"). You can isolate that file with:

= Table.SelectRows(Source, each [Name] = "HeaderTemplate.csv")

Then read its first row and promote it to headers:

= Table.PromoteHeaders(Csv.Document([Content]{0}, [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]))

Once you have the proper headers, apply them to the rest of the combined data using Table.ColumnNames and Table.RenameColumns, ensuring column count matches. This gives you a clean, deduplicated table with the correct schema even as new files are added to the folder.

Best regards,