Get data from Folder, but excels with different column headers

[T-Pan]

Hello everyone,

Every week i update an power bi report with excel files which i receive from the data department. So i dicided to try to import the data from "Folder". The excel files have the same number of columns but in every excel file the first column header is always different in every excel file. So when i try to combine the excels it takes as headers of new table the column headers of first Excel an not show the values of this column from the other excels, instead it shows "null".

I have found several videos in youtube about combine excels from same folder with different number of columns but not something about my issue.

[Anonymous · Accepted solution]

Hi @T-Pan ,

Please try this way.
I will show you my sample data first:

Then, I do the append query first:

The results are as follows:

Then do a column merge:
Click both column "Date_2023" and column "Date_2024":

The final results are as follows:

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

[T-Pan]

Also i want to ask if there is any similar way to get data from Folder, but a folder which located in Sharepoint, so every time i enter a file automatically append in my table?

[T-Pan]

Thank you very much. Your solution is what i was looking for. 

[spinfuzer]

Maybe in the Transform Sample File Use Headers as First Row (or whatever you need to do in order to make the first row(s) the same).  Then in your main query (where all the files are combined) fix the headers from there.