Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power Query Combine files containing different Date column headers

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

SourceCustomer15/05/202416/05/2024

File 2 Column Headers

SourceCustomer16/05/202417/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.

SourceCustomer15/05/202416/05/202417/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!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi,

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

    hello Anonymous , 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.

2 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi,

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

    hello Anonymous , 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.