Combine similar (not exactly same) files in a folder

[mbegg]

Hi, 

I have 78 .csv files in a folder. 

They all contain a subset of the same 13 columns. Some files contain only 4 columns while one file contains all 13 columns.

The columns are not layed out consistently. E..g. File 1 has [Weight] in Column A while file 2 has [Date] in Column A.

There are 34 unique formats. 

What is the most efficient way to merge the 78 files into one dataset?

I could set up 34 folders (for the 34 unique formats) but hoping there is an easier way.    

[MarcelBeug · Accepted solution]

If the column names are all the same (if they are present), you can add a "select columns" in the query that transforms the sample file.

Syntax:

Table.SelectColumns(table as table, columns as any, optional missingField as MissingField.Type) as table

As missingField use MissingField.UseNull

This will insert the column with null values.

If the column names are not the same, you may need to apply renames first, using Table.RenameColumns, with syntax:

Table.RenameColumns(table as table, renames as list, optional missingField as MissingField.Type) as table

In this case, you can use MissingField.Ignore

[mbegg]

@MarcelBeug Thanks for your help. The columns have the same name so your "Select columns" suggestions worked nicely!