Joining hundreds of tables with some similar and some distinct column names

[sarahkane]

Hi,

I am trying to combine hundreds of tables together where there are some formatting differences between column headers, and some distinct column headers. For example: one table might have the column headers name and email, while another table will have the column headers of Name, EMAIL, and Location. 

I have been able to follow this youtube video: https://www.youtube.com/watch?v=wKglApDFMog which allowed me to join the tables if the column header was the exact same, but for the example above, the Location column header, it displays all the data that should be populated as NULL. 

Please let me know if my question is confusing or if you have additional questions.

Thank you!
Sarah 

[v-stephen-msft · Accepted solution]

Hi @sarahkane ,

According to your description, I did a test and you can specify columns in different tables to be merged by the following code.

let
    Source = Table.Combine({Sheet1, Table.RenameColumns(#"Sheet1 (2)",{{"headers name","headers of Name"},{"email","EMAIL"}})})
in
Source

If the problem is still not resolved, please provide detailed error information and screenshot of the results you expect. Looking forward to your reply.

Best Regards,
Stephen

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