Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!
Solved! Go to 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
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!
Hi,
Can you add some example data to your question? I'm not sure how ypur data is organized.
Artur