010. Map Headers to Merge Files with POWER QUERY (List.Accumulate)

Map Headers to Merge Files with POWER QUERY: Many times, we can't trust the different departments that are filling Excel sheets! We often get different column orders and names. The issue with Power Query is that it would automatically know which header is for which data. Fortunately, there is a solution!
In this tutorial, we will use a mapping table and a function called List.Accumulate to go through the mapping table and assign the right header for each column in each file. Based on this Power Query will aggregate the data for us! Et Voila!
Link to Download the workbook: mega.nz/file/rDxwhSJC#fV9Vv64...
Content: map headers to merge files, power query advanced tricks, automatically map headers of different files, how to map header with power query, map headers in excel, automatic header mapping power query, import files with different header in power query,different headers in power query, change headers with mapping table, power query advanced mapping, power query files with different headers, power query combine files with different headers, combine excel files with different headers
#Excel #MsExcel #powerquery

[@lijumathew5203]

Thanks bro for making it so simple to understand. You are a life saver. I've spent my entire day trying to figure out how this works. Thank you.

[@EXCELWIZARDINMINUTES]

You are most welcome! 😊

[@vvv1160]

​@@EXCELWIZARDINMINUTEScan u share the formula

[@LotfyKozman]

Excellent episode with clear explanation. 🌹

[@EXCELWIZARDINMINUTES]

Thank u for the feedback 😊

[@idaeinjaw2139]

very detailed and useful tutorial. thanks!

[@EXCELWIZARDINMINUTES]

Thank you very much! :)

[@aatsw]

Awesome!

[@EXCELWIZARDINMINUTES]

Thankss a lot! :)

[@Ratnakumarwrites]

great Bro.. thanks

[@yadavbabbu5218]

Great info.

[@EXCELWIZARDINMINUTES]

I am glad you liked this episode! Thanks for the kind comment ! :)

[@Sumanth1601]

Nice video.. Helpful

[@EXCELWIZARDINMINUTES]

Thanks a lot! I am very happy that you like it! There will be many more to come as I am trying to share my knowledge with the community. Please do not hesitate the share the channel link to whoever is interested in the topic :)

[@alessandratorsello9511]

Hi I love your video tutorials, Although, this time, I don't seem to understand the basis of your video. I understood that to create a mapping table I must first manually list the headers ("BEFORE" column) of all the tables I am working on and then create a second column ("AFTER") with the names I want to transform the "Before" Headers column into. How can I handle this procedure if my tables are many? Won't I waste too much time collecting all the headers? What did I miss/not understand about your instructions? Thank you for your possible reply

[@EXCELWIZARDINMINUTES]

This is a useful technique when you have files with multiple headers (different names) and not in order. Instead of opening each file and changing the header, you can create a mapping table and add to it when you have new files. The key is that the set up is one time and then when you get new data, you just refresh. (Sometimes you work with excel trackers that are set up and hard to standardize. Merging those files manually will take a long time - therefore you have list.accumulate) As an example, if you have shops in mutiple countries with different languages, you can use this technique to ask the different shops to give your their numbers. A spanish person will be a lot more comfortable if the headers are in Spanish vs a French person will prefer them in French. You can send them the templates and collect the results in this way. Another example, if you collect attendance of different departments in Excel. Each department might have diff headers and they cant change them since the file is linked to other files. In this case, you set up a header table once and use power query to combine. In case they add/move columns in the sheet, your query won't be affected if set up right. I hope it clarifies. Grazie mille per aver guardato le leccioni :)

[@alessandratorsello9511]

@@EXCELWIZARDINMINUTES THANX A LOT

[@ohdjrp4]

This is great! But it's hard to grasp how it works. Better, strictly instruct the people inputting the data on tables not to disturb the table structure, so everybody is happy, right?

[@BusinessOperations-im1hp]

Hi I got an error and tried to debug syntax but did not fined the error. Could you check if I misspelled something, pls. Query Step: = Table.TransformcolumnNames(MyData, each List.Accumulate (Table.ToRecords(HeaderMap),_,(state, current)=›if Text.Upper(state)=current[Header.From] then current[Header.To] else state) Error: Expression.SyntaxError: Token RightParen expected. Table.TransformcolumnNames(MyData, each List.Accumulate (Table.ToRecords(HeaderMap),_,(state, current)=›if Text.Upper(state)=current[Header.From] then current[Header.To] else state) ^

[@EXCELWIZARDINMINUTES]

I can see that there is possibly a missing ) at the end of the expression. I think it is state)) and not state)

[@orchidg4258]

What if your Excel data files to combine are not tables? I've tried Worksheet_Sheet but I get the file names, not the data. Thanks.

[@ArunJebastin]

I tried the steps, but no changes happen. How to troubleshoot? any other way to validate whether steps are correct or not?

[@EXCELWIZARDINMINUTES]

Check if you have the same casenin your table. Here is how i would troubleshoot. Try the steps with 1 header, simple one like "hi" to "bye". This way, it will be easier to see the problem. Put only one header in the table.

[@ArunJebastin]

@@EXCELWIZARDINMINUTES thanks for the reply. I located the issue. Removed Text.Upper since my mapping data is not upper case

[@ArunJebastin]

@@EXCELWIZARDINMINUTES ​additionally how to handle if there are variable headers? Like some has 5 others more columns. From other tutorials i can merge differential column counts, however, if I use list.accumulate then could not able to use the other step. If possible please advise how to merge multiple files with different columns and as well as different names for same columns. This video explains how to merge different columns kzbin.info/www/bejne/ZmrXp5yXlJ2soas

[@EXCELWIZARDINMINUTES]

@@ArunJebastin Since it happened to me with upper and lower case, this is why I guessed that this is the issue :) Glad you caught it fast!

[@EXCELWIZARDINMINUTES]

Now, regarding your question about varying headers, this is something advanced that requires coding. What you need to do is to go into the uploading steps and make changes using list.union and List.transform. It is hard to explain here but have a look at this resource - maybe it can help: datachant.com/2017/02/07/power-bi-pitfall-9/

[@abhishekawari919]

I tried but it throw an error that updated value not allowing duplicate… not sure why for me

[@EXCELWIZARDINMINUTES]

I suggest you try with a small example where you replace 1 header first. Then u can slowly expand. U might have written something wrong in the formula (1 letter can make a difference) or have a problem with ur mapping table

[@abhishekawari919]

@@EXCELWIZARDINMINUTES not sure in single table it works fine for me , i have again checked my full piece of code i dont find any gap…

[@EXCELWIZARDINMINUTES]

@abhishekawari919 i am not sure what you mean by single table

[@vvv1160]

Where is the formula??

[@vvv1160]

Kindl share rhe formula

[@prasoonsurendran7916]

Hi, this was very helpful, though I'm getting null values for most of the columns in 1 of the 3 files. For the other two it works fine. It is driving me crazy! 🥲

[@EXCELWIZARDINMINUTES]

Yes this is quite a complicated exercise. Now check if everything is written exactly the same way (data capital vs small letters, no extra spaces, the formula). Then draw this on an excel manually and go over how it is replacing. Hopefully u will find the issue