Combine tables with columns that vary between source files

[SnowAus]

Hello, I hoping for some guidance on a problem i have - I'm relatively new to this (but enthusiastic!) 

My organisation has several hundred meters which report interval consumption monthly in a large .XLSX file. My issue is that the way this file is exported does vary and when they files combine, the data is not correct. This occurs because, due to the nature of the metering on site, if meters are decommissioned, they no longer appear in the file (ie, their column is no longer there). Also, we often add meters, and these are inserted by the reporting software relative to other meters, ie, the new column could be in the middle of the dataset. I thought it best to try to represent this diagramatically, see below:

What the files look like over a few months

I know why this is occuring but i am unsure if this can be configured to achieve my desired output. Ideally, i would like to set this up to reference headers - i know the reasons why this is difficult (ie, non-union compatible) but i am not sure how to solve the problem - i am sure that i would not be 

Desired outcome would be all files in a single folder, refreshing as new data is added and tied back to the headers for each file for subsequent analysis. 

Any hints on where to start would be much appreciated - Thanks!

[v-chuncz-msft]

@SnowAus 

You may add Table.PromoteHeaders in The Advanced Editor.

[SnowAus]

Hello

thanks for your response.

I am not sure i follow how this could work. I am already promoting headers, but this still does not align the columns in the files where non-identical columns exist.

Can you elaborate at all?

Regards