Forum Discussion
Combine Data from a folder where contained files have a different number of columns
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!
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
3 Replies
jbwtpMemorable Member
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
ah, this is perfect, thank you!
artpilResolver II
Hi,
Can you add some example data to your question? I'm not sure how ypur data is organized.
Artur
