Combine different sources with different column names

[MCDyna]

Hi all,

I found a lot similair, but not my exact situation.

The case is that I want to combine multiple Excel files. It comes from different stores.

They all have the same information, but they name it differently (well, their system does)

So they all have a certain planned end date, and a type of priority; but one calls it 

PLANNED DATE en the other TARGET DATE

They cannot change this. What is the most sustainable way to fix this?

So not the quick solution, but the best one 🙂

[KNP · Accepted solution]

Hi @MCDyna,

Attached is a PBIX file that can handle dynamic appending of tables.

This is a very simple example so will likely need to be modified to suit your scenario and may require further explanation, which I'm happy to provide. 

Have a look at it and see if it makes sense and let me know if you have questions.

Basically, I've set the append to ignore the headers by demoting and skipping them and then adding headers back in using a "preferred" list.

I hope this helps.

[KNP · Accepted solution]

Hi @MCDyna,

Attached is a PBIX file that can handle dynamic appending of tables.

This is a very simple example so will likely need to be modified to suit your scenario and may require further explanation, which I'm happy to provide. 

Have a look at it and see if it makes sense and let me know if you have questions.

Basically, I've set the append to ignore the headers by demoting and skipping them and then adding headers back in using a "preferred" list.

I hope this helps.

[MCDyna]

Wow! You are amazing! Very grateful!

I understand how you set it up, but I do not know (yet) what to do precisely to change the headers. Is it something like a macro that runs it?

I could use some more assistance so I can adjust it to my situation. 

[KNP · Accepted solution]

Hi @MCDyna,

I'm happy to help explaining how the headers work.

In the file I provided, this query goes to one of the files (queries) to get a list of column headers. I just picked one of the existing queries but this could come from anywhere.

// PreferredColumnNames
let
    Source = Table.ColumnNames(StoreA)
in
    Source

The final query, the "Appended Query" step demotes the headers from each table, then skips the headers, and finally combine the tables. 

The Custom1 step uses a List.Zip to combine/zip the two lists together, the lists being 'Table.ColumnNames(#"Appended Query")' and 'PreferredColumnNames', the Table.RenameColumns takes the result from List.Zip to change the column names. The first list being the 'from' and the second being the 'to'.

// Final        
let
  Source = StoreA,
  #"Appended Query" = Table.Combine(
    {Table.Skip(Table.DemoteHeaders(Source), 1), Table.Skip(Table.DemoteHeaders(StoreB), 1)}
  ),
  Custom1 = Table.RenameColumns(
    #"Appended Query",
    List.Zip({Table.ColumnNames(#"Appended Query"), PreferredColumnNames})
  )
in
  Custom1

Let me know if you have any other questions.

[MCDyna]

Hi @KNP 

It has to be in this particular order with this approach, can you adjust the script so I can fill in 

original column name and change it to desired column name?

[MCDyna]

Never mind, got it. I was looking for:

= Table.RenameColumns(#"Type gewijzigd",{{"EVT_CODE", "Workorder"}, {"EVT_CLASS", "Class"},

[Anonymous]

Hello @MCDyna 

If you want combine multiple excel files with same contex but different headers. You download the excels rename the headers and append them together.

[MCDyna]

Thank you for your input, but this won't work. Well, unless I do this every month when I update my files.