Renaming Fields (Mapping) from multiple Data sources

Question

I have multiple Data sources . Here in the data file attached I Have majorly 5 columns and 3 data sources. I want to rename the Month columns like forecast 1, Forecast 2, Forecast 3. Also after renaming column I want to union the data in such a way that RM 24242 will have 3 records in my output table. 
The file attached is Just for test purpose. In actual there are 12 Month columns. Its actually a rolling forecast file which generates each month. But there will be fixed number of columns every time that is 12 which we need to rename from Forecast 1 to Forecast 12. If you can help me  with the logic of these three files it would be really greateful. 
I am able to do this manually but there are around 12 months and have 12 data sources. Is there any macro or something where I can automate this process

Test Data.xlsx

aatalai · Answer

@prakhar021 if you've synced your sharepoint with your local machine this would work. Otherwise you will need to use the sharepoint input tool and create a macro to look at different files and different names (or have a different tool per file)

Different files, you could use the similar method. you would need to get the file paths of these files (can be done via directory tool or manually pasting the file paths into a text input tool, or if you know the naming convention generate them using a formula tool) and input them into the macro

prakhar021 · Answer

What to do if the file exist in Sharepoint and the data is in different excel files ?

aatalai · Answer

@prakhar021 brought the sheet names and the file path, then put it into a batch macro to read the files, removed the nulls and used a dynamic rename to rename the fields, please take a look and let me know if this does the trick

Renaming Fields (Mapping) from multiple Data sources.yxzp