Forum Discussion
Helper IIFuzzy 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?
Also asked in Reddit and they got me a solution: https://www.reddit.com/r/excel/comments/1cyyley/extract_text_before_delimiter_in_power_query_but/
5 Replies
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 - RADACADBest Regards,
Yang
Community Support TeamIf 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
sarah2I 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,
Yang
Community Support Team
