Power Query Combine files containing different Date column headers

[AliPoTD]

Hello all,

Please can I ask for your help with an issue I am having combining files in Power Query.

I have several Excel files which have a number of columns with a date as the header.

Each day, another Excel file is received with the same layout except that it will have replaced a Date column with a new date column containing a different date.

When I try to combine them in Power Query, I edit one of the files in the folder in the "Transform Sample File", but when this is then used to merge the multiple files in the main query, I receive an error stating that it is missing a particular Date column. 

For Example:

File 1 Column Headers

Source

Customer

15/05/2024

16/05/2024

File 2 Column Headers

Source

Customer

16/05/2024

17/05/2024

If i use e.g File 1 as the Sample file, i then get an error stating that it can't find column "15/05/2024". This will occur with any of the files as they all differ slightly in which dates they show.

What I need is for the end result to be e.g.

Source

Customer

15/05/2024

16/05/2024

17/05/2024

I'm guessing I need to utilise a List in some way, but I'm not sure of how to do this.

Any advice greatly appreciated, many thanks!

[v-xinruzhu-msft]

Hi,

Thanks for the solution @HotChilli  provided and i want to offer some more information for user to refer to.

hello @AliPoTD , based on your description, you can refer to the following solution.

in power query, after combing the file, click the invoke custom function step and remove the steps below it.

Then select transform file column, and select remove other columns

Then expand the column.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

It looks like it would be better to Unpivot the date columns in the sample file.  That will prevent loading errors and you can do any transforms required from a common base.