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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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