Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

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 but in different order. I can upload a folder of all the excel documents and use table combine so all the files are merged. Obviously columns with the same headers are then combined, which is what I want. 


My problem is that there are some files that have a header like "Question_90" and then another will say "Question_1818" (every document will have a different "_number") but I want both of those columns to be combined called "Question". I know I can do this manually by combining columns or edit in excel to take off the "_number". Is there a way to do it in Power Query so I don't have to do it in every document & it will automatically do it to new files added to the folder?

Community Support
Community Support

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,
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

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.

Hi, @sarah2 

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

Best Regards,
Community Support Team


Here's Table A

Column AColumn BColumn CColumn DColumn EColumn F_0908Column G_2929Column H_291


Here's Table B:

Column AColumn BColumn F_9203Column G_0098Column H_45


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!


Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.