Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mstanford
Frequent Visitor

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!

 

 

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable 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

 

View solution in original post

3 REPLIES 3
jbwtp
Memorable Member
Memorable 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!

artpil
Resolver II
Resolver II

Hi,

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

Artur

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors