Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, sarah2 

In Power Query there are append and merge two ways to combine data, field names need to be changed can be modified directly by double-clicking. You can refer to the following documents to learn how to use them.

Merge queries overview - Power Query | Microsoft Learn
Append queries - Power Query | Microsoft Learn
Append vs. Merge in Power BI and Power Query - RADACAD

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

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!