Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
18 | |
15 | |
13 | |
8 | |
8 |