Forum Discussion

sarah2's avatar
sarah2
Icon for Helper II rankHelper II
2 years ago
Solved

Fuzzy Match or Replace Value in Header

Hello! I'm thinking this isn't possible, but maybe you all will think of it in a different way and have a good solution. I have multiple excel files that have the same or similar column headers bu...
Need Help
sarah2's avatar
sarah2
Icon for Helper II rankHelper II
2 years ago

I found a method that could work but I'm holding out hope there's a way to do it in power query so I don't have to do it by hand to every excel file:
In each excel file if I add in a new top row with the formula =TEXTBEFORE(A1,"_",1,0,1)    It results in what I want which is getting rid of the "_number" part and when I put it in power BI it combines the columns and I can filter out the old headers row. 

Update: I got close a few times in power query by using the sample file to use first column as first column (not headers) then extract Text before deliminator (the _) then promote headers. This would work if I didn't have different numbers of columns for different excel sheets.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, sarah2 

Can you post a picture describing your needs please, a little confused about your needs.

Best Regards,
Yang
Community Support Team

  • sarah2's avatar
    sarah2
    Icon for Helper II rankHelper II
    2 years ago

    Sure! 

    Here's Table A

    Column AColumn BColumn CColumn DColumn EColumn F_0908Column G_2929Column H_291
    DataDataDataDataDataDataDataData
    DataDataDataDataDataDataDataData
    DataDataDataDataDataDataDataData
    DataDataDataDataDataDataDataData

     

    Here's Table B:

    Column AColumn BColumn F_9203Column G_0098Column H_45
    DataDataDataDataData
    DataDataDataDataData
    DataDataDataDataData
    DataDataDataDataData

     

    My end table in Power BI should have the headers:

    Column AColumn BColumn CColumn DColumn EColumn FColumn GColumn 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!