Combine different sources with different column names

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.

 

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.