Sure!
Here's Table A
| Column A | Column B | Column C | Column D | Column E | Column F_0908 | Column G_2929 | Column H_291 |
| Data | Data | Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data | Data | Data | Data |
Here's Table B:
| Column A | Column B | Column F_9203 | Column G_0098 | Column H_45 |
| Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data |
| Data | Data | Data | Data | Data |
My end table in Power BI should have the headers:
| Column A | Column B | Column C | Column D | Column E | Column F | Column G | Column H |
with data from both Table A and Table B
The real tables have like 40-60 columns (not always the same number of columns), and there are over 100 excel files. I want to plop all the excel files into one folder and have power bi combine them. The problem is that I want Column F_0908 from Table A to merge with Column F_9203 from Table B because they are both "Column F". I'd like this to be automated because there are so many files and there would be a lot to merge manually.
The only solution I have found is going into each excel file to take off the "_number" from each header so they would say the same thing before uploading into Power BI.
The method I wish worked was to work in the sample file after the folder is uploaded to Power BI. Here I could demote headers, Extract>Text Before Deliminator (deliminator being "_") which makes it say "Column F" then promote headers. This would be the perfect solution except that my files have a different number of columns.
I want this to be as little work as possible for each new file we add to the folder in the future. Any ideas would be appreciated! Thank you!
