My task is to fully automate procurement dashboard. The procurement team gets 3 excel files which contains only one common column. All other columns are different. In 1st excel - Purchase Doc., and in other 2 it is PO No. I know how to merge files when they have the same column headers. But my case here is different.
I need to merge this 3 excel files. I’m stuck here because I have used get data from folder option but after that I could only create one table. Plus I can’t use any other get data option because next month another set of raw files will come which needs to be added to the folder.
what I tried is after clicking on GetData->Folder->Transform. I removed all other columns just Binary and file name were present. Now on right clicking on Binary, there is an option of Add as new query. So i created 3 queries of my 3 tables. This would work now. But the thing is I don’t know by this way of Add as new query, I can automate the process and make it work again if i put more files in the folder next month.
I tried to watch every single video on youtube but they only explain merging when two files have same column names or when the columns are reordered or inconsistent(same column but slightly different headings).
Last solution Im thinking is to create a python code which would merge the 3 excels for me and place the merged file in the folder automatically.
