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
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 :)
Can I use the List transform method to combine files with not only different headers but different number of columns with no unique column/column header between them. My clinical trial databases all make headings and number of columns as per their own company. No one column header or internal info matches the other databases. But they all have the same trials in them. I just need to throw the duplicates and keep unique records as one database may be more up-to-date and have listed more trials and the other might be missing some trials.
everything can be done but i guess in your case, the complexity is big. try to use some of the power steps and rule to make your databases as streamlined as possible before you combine
@@EXCELWIZARDINMINUTES Thank you! What i will do is clean up the data first by splitting the 'identifier' column into NCT ID and other unnecessary extra text/numbers. That way I will have the NCT ID as a common column among all the databases. AND THEN COMBINE THE WORKSHEETS.
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.
@@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 ruclips.net/video/09tvia_8ykI/видео.html
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/
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?
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 :)
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
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) ^
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! 🥲
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
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.
You are most welcome! 😊
@@EXCELWIZARDINMINUTEScan u share the formula
Excellent explanation 👍
thanksss. It s a complex topic, glad it s clear :)
Excellent episode with clear explanation. 🌹
Thank u for the feedback 😊
very detailed and useful tutorial. thanks!
Thank you very much! :)
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
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 :)
@@EXCELWIZARDINMINUTES THANX A LOT
Can I use the List transform method to combine files with not only different headers but different number of columns with no unique column/column header between them. My clinical trial databases all make headings and number of columns as per their own company. No one column header or internal info matches the other databases. But they all have the same trials in them. I just need to throw the duplicates and keep unique records as one database may be more up-to-date and have listed more trials and the other might be missing some trials.
everything can be done but i guess in your case, the complexity is big. try to use some of the power steps and rule to make your databases as streamlined as possible before you combine
@@EXCELWIZARDINMINUTES Thank you! What i will do is clean up the data first by splitting the 'identifier' column into NCT ID and other unnecessary extra text/numbers. That way I will have the NCT ID as a common column among all the databases. AND THEN COMBINE THE WORKSHEETS.
great Bro.. thanks
I tried the steps, but no changes happen. How to troubleshoot? any other way to validate whether steps are correct or not?
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.
@@EXCELWIZARDINMINUTES thanks for the reply. I located the issue. Removed Text.Upper since my mapping data is not upper case
@@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 ruclips.net/video/09tvia_8ykI/видео.html
@@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!
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/
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?
Great info.
I am glad you liked this episode! Thanks for the kind comment ! :)
Nice video.. Helpful
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 :)
Awesome!
Thankss a lot! :)
I tried but it throw an error that updated value not allowing duplicate… not sure why for me
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
@@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…
@abhishekawari919 i am not sure what you mean by single table
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)
^
I can see that there is possibly a missing ) at the end of the expression. I think it is state)) and not state)
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.
Where is the formula??
Kindl share rhe formula
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! 🥲
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