Combine Data from a folder where contained files have a different number of columns

[Mstanford]

Good afternoon everyone,

I'm working on a project where I need to combine a large number of excel files stored in a folder. The hangup is that each of the files contains a different number of "attribute" columns, with a variety of names. I want to combine the files in such a way that all of the attribute columns are retained, but I can't do that with the standard combine function as I have to pick a transform file that omits all of the attribute columns it doesn't contain. Any help would be appreciated!

[jbwtp · Accepted solution]

Hi @Mstanford,

in the main and Transform query remove all references to column names - typically it would be #Changed Type step - until it comes to the Append step.

If you followed a typical way of creating it (i.e. using the export assistant), the code also has Expanded Columns step, which is also a problem as you nee dto list column names there (and it typically refers to the first file structure). Instead, you need to do something like this:

Combine = Table.Combine(#"Added Custom"[Data])

Where #"Added Custom"[Data] refers to a column with the exported data from the files (the step imideatelly before the #"Expanded Something").

If you need more detail, please share your main query code in the part which preceeding the Expanded step.

Kind regards,

John

[Mstanford]

ah, this is perfect, thank you!

[artpil]

Hi,

Can you add some example data to your question? I'm not sure how ypur data is organized.

Artur