Combine Files with Inconsistent Headers No Mapping Table
ฝัง
- เผยแพร่เมื่อ 14 พ.ย. 2023
- Combining Data from with Totally inconsistent Headers is aa very common task we do in Excel.
The Classic technique is to create a Mapping Table and use it to rename the columns in All Files.
In this tutorial I show you how to combine files with inconsistent headers Without the need of creating a mapping table.
You can Download the Exercise Files and Follow along by clicking on the link here below:
You can also read my blog post and step by step guide by clicking on the link:
officeinstructor.com/power-qu...
To book a Corporate Training anywhere in the world fill the Request Form:
www.OfficeInstructor.com
Most awaited video on PQ. Thanks Nabil.
Most welcome!
Dear Nabil,
Your excellent video inspired me to develop, with dynamic formulas, the grouping you achieved in Power Query:
=LET(Africa,REDUCE("Continents",SEQUENCE(ROWS(Africa[Countries])),LAMBDA(a,b,VSTACK(a,"Africa"))),
Asia,REDUCE("Asia",SEQUENCE(ROWS(Asia[Country])-1),LAMBDA(a,b,VSTACK(a,"Asia"))),
Europe,REDUCE("Europe",SEQUENCE(ROWS(Europe[Nation])-1),LAMBDA(a,b,VSTACK(a,"Europe"))),
Countries,VSTACK(Africa[[#All],[Countries]],Asia[Country],Europe[Nation]),
Capitals,VSTACK(Africa[[#All],[Capitals]],Asia[Main City],Europe[Capital]),
Populations,VSTACK(Africa[[#All],[Populations]],Asia[Population],Europe[Residents]),
AreaSqKm,Africa[[#All],[Area SqKm]],
IFNA(HSTACK(VSTACK(Africa,Asia,Europe),Countries,Capitals,Populations,AreaSqKm),""))
I hope you like it. 🤗
Thank you my friend for your contribution
Thanks Nabil, that is a fine technique you showed us there! :-)
Glad you liked it and thanks for watching
Simple steps and insightful, as always. Thanks Nabil
Excellent resolution. I think Power Query is still superior to Excel here. These models are easier to do this way. Thanks for this great explanation Nabil.
You are welcome! dear friend
Very good, useful! Thank you Nabil!
You are welcome!
Thanks for watching
Brilliant ,, thanks a lot
You are welcome!
Hi Nabil, please do more excited Power Query videos like this.
Sure I will
Excellent video
Thank you very much. Great share.
Glad you enjoyed it
I follow you constantly and benefit greatly from your educational videos. Really thank you and appreciate for the super videos and solutions. But you don't share your work files at all. In fact, it would be much more helpful if you shared your working file. We will have the opportunity to practice while watching you and we expect you to share the files.
Hello, I am glad you find value in my tutorials.
Due to very low interaction on my videos I do not post the source files regularly as I used too. I know they are really valuable.
However, for this tutorial you can Download the Exercise File from my blogsite:
officeinstructor.com/power-query-trick-for-combining-files-without-renaming-table/
اول واحد يذتب تعليق و يعمل 👍
مشكور
Habibi Shokran
@@Officeinstructor 🙏🙏🙏