Power query: can't refresh as new source has different column

Question

I created an Excel file that uses Power Query to generate a table. The table is created by extracting data from a folder where I deposit CSV files. It works fine when the CSV files added to the folder have the same column name, position or number. However, it's not viable if there's a new column added or if there are changes to the column position of newer CSV files. Currently, if newer files have new columns added, I will delete the newly added column in that file. Are there any better ways to do this on the Power Query Editor so that I do not need to keep modifying new files before depositing them into the folder?

For example:

Old File (used this first CSV file in the folder to generate the table)

Currently Newer csv files has one more column added (RFQ ID @ Column A)

Will appreciate the help. Thank you

Answer 1

Show us your Mcode (Right-Click the query and copy, paste it here).

Andreas.

Answer 2

Hi,

I'm Ajibola, an Independent Consultant here and a Microsoft user like you. I don't work for Microsoft and cannot access any of your data on their system.

There is a way to handle varying column structures in Power Query without manually modifying the CSV files each time you add them to the folder. Power Query has a feature called "Combine Binaries" that can help you achieve this.

When you use "Combine Binaries" in Power Query, it will dynamically detect the columns from all the CSV files in the folder and merge them into a single table, even if the columns vary in number or position. Here's how you can do it:

1. In Excel, go to the "Data" tab and click on "Get Data" > "From File" > "From Folder."
2. In the "Folder" dialog box, browse to and select the folder where your CSV files are located. Click "OK."
3. Power Query will open and show you a preview of the data in the folder. Click on the "Combine" dropdown in the "Content" column and select "Combine Binaries."
4. Power Query will then process all the files in the folder, detect the columns, and combine them into a single table.
5. Once the data is loaded, you can further clean and transform the data as needed.

By using "Combine Binaries," Power Query will automatically handle variations in column structures, making it easier for you to work with new files without manually modifying them beforehand.

Kind regards Ajibola