Excel Unpivot Data with Multiple Headers (Multiple Row levels into Columns with Power Query)
Вставка
- Опубліковано 4 сер 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Stop wasting time creating complex formulas to transpose data in columns with multiple headers to get proper tabular data. With Excel's Power Query you can easily create a dynamic solution to transform your report-style data into a dataset you can use to run Pivot Tables on or do further analysis on with formulas.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/unpivot-adv...
Unpivot means to create rows from columns. For example if you have a sales report and the months Jan - Dec are each reported in its own column. By Unpivoting we "shrink" this report with 12 columns for the months into a tabular format where all months are consolidated in one column and the data is shown in the rows. This way your data is properly structured for further analysis.
📝 Video Overview:
- Tackle the challenge of converting complex reports into tabular datasets.
- Learn to handle datasets with multiple headers and totals embedded within.
- Discover the dynamic solution to ensure automatic updates with new data additions.
- Get detailed guidance on using Power Query to unpivot and clean your data.
💡 Key Points:
- Simplify complex data for easy analysis and pivot table creation.
- Learn dynamic techniques to keep your data analysis up-to-date.
- Transform your approach to data manipulation in Excel with expert tips.
LINK to Unpivot video: • Convert Columns to Row...
00:00 Purpose of UnPivot in Excel
02:27 Prepare data for Excel Power Query
04:44 Use Power Query Editor to Unpivot Data
10:06 Test Dynamic Solution when adding new data
11:26 Remove empty cells from report
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/unpivot-advanced-file
Power Query: the greatest tool since the PivotTable! Thanks, teammate : )
Couldn't agree more, teammate!
I learnt much from you both during the last 4 years, do really appreciate your overwhelming touch in my caree, owe you much
You guys rock and i watch you both all the timee!!!
OK, I give up ; totally confused. My screen doesn''t look like your screen
Learn lot from both of you . Salute !!
Leila you are insane, I followed many excel Chanel but yours is the most clear and simple to follow, I improved a lot myself with you. Thanks
I am halfway through your course and it has paid for itself in one day in time savings!
This is such a High Quality Tutorial, including the workbook, it is very satisfying to see how much love and effort you and your team put in doing this, some day it'll pay off.
Wow, thank you!
Leila, Thank you for posting these awesome videos. Your impressive teaching style is so easy to follow. I love Excel, but I'm mostly self taught, which can make it difficult to understand the lingo, etc., in certain tutorials as I troubleshoot. Your videos are so clear that I find myself watching them just for fun. Thank you for rekindling my love for Excel. Be well!
Thank you! I'm glad you like the videos.
I had totally different Financial Ledger data in multiple row headers. Was breaking my head to manually change to tabular format, then came across your video. Followed your steps and yo!!...structured it beautifully in a simple tabular format. Thank you so much for so clear and concise instructions.
I've watched hundreds of Excel videos over the years, and this is probably the best I've ever seen; the perfect balance of context, explanation, insight and detail (and exactly what I needed help with today). Thank you, Leila.
Wow, thanks Greg! Glad it was helpful.
After two days of googling for tips you gave me exactly what i needed! And I love your style of presentation the best. I needed to sum up data for each cultivar of plant across all the days of the year. This makes it easy to make rows of each data point because of all the blanks for most plants on most days and because further analysis do better without blanks taking up space and memory. Eventually i want to move it to an access table and it is setup this way and keep the informative columns to the left of the actual data. Other databases like GRIN-Global are this way also but original data gathering is still best with excel condensed worksheets. THANK YOU SO MUCH! I will look to subscribe.
So wonderful. Thank you so much Leila. I used to do lots of copy/paste, with a high risk of errors, to get this result. I do as well, appreciate a lot the trick of the Split button. Very useful. 👍👍
Amazing, and, ohhhh, I would have loved this 20 (ok, 35) years ago. I used this tonight to help a PhD candidate better organize her qualitative data. (I also used a similar technique she describes in another video.) Thank you, Leila!
Time spent with Leila learning new ways and techniques in excel is time well spent. Thank you Leila.
Glad you think so!
Great content! Thank you Leila for such a clear-cut video! Saved me A LOOOOT of time and multiple headaches. Big addition to my data cleaning know-hows
Amazing, thank you. I have been struggling with Multiple Headers, it has given me a breath of fresh air. Thank you again.
Thank you very much Leila! This is definitely a life saving function!
Thursdays mornings with Leila is 'must-attend' classroom time … and this morning was another excellent presentation (no surprise) in terms of delivery and content … and a very timely topic for me. Thank you Professor Leila …
PS
A 'Pro' makes things look easy … and Leila is an 'All-Star' Pro … the Star of the 'Show' … she is my hero (heroine???) as I toil away in the minor leagues trying to get a little better each day … all her videos provide excellent training techniques … thank you … thank you … thank you.
😘
Thank you! Spent the entire day untangling a large table. This video made the same thing possible in 15 minutes!
Great one Leila. Thank you for sharing your knowledge.
It gives me my feed of the day about the Power query and appreciate your detailed explanation. I encourage you to upload more videos
THANK YOU! This video saved my sanity! I am fairly new to Power Query and this was extremely easy to follow and understand!
Clear, concise, High Quality material. Thank you for this
Thanks Leila, this answered all my questions about multiple headers in a table
As always, you have the perfect video to a niche question. Thank you!
Hi Leila.. great video and transformation. I'm in your course (love it) but have not yet reached this lesson. Thanks for the preview and the great tips and tricks shown in the video. Thumbs up!!
This is amazing! From presentation skills to content!! Thank you!
Oh my god, my problem since years is solved 🤓 Leila, another time a great solution. You are my angle! Thank you sooo muuuch. God bless you. 🌻🌻🙏🙏🙏
You're welcome 😊
This is brilliant!!! Not sure, how can anyone dislike this video? May be they didn't know about Excel ;)
It's awesome what we could learn with you Leila
Thanks a lot
You are simply super! I really appreciate your efforts in sharing the knowledge to all!
Thanks! This was so easy and elegant and I imagine the tricks you used will come in handy in unexpected ways in the future. You rock!
Thanks a ton for such a beautiful and simple explanation. This is really helpful.
Brilliant video. Every action beautifully explained. Thank you Leila
Thank you for always coming in clutch ahead of my deadlines lol We love an Excel Queen!
Amazing Leila, these complex situations really needed to learn about
Thank you SO MUCH, Leila 🙏 This tutorial has saved me from manually unpivoting Multinational Sales Data of 14 products with 3 levels of headers (Countries, Ports, Months).
Glad it was helpful!
This was an absolute godsend! Thank you!
This blew my mind. Thank you!!
I ended getting your Power Query course as a result of your videos on Power Query. I am so glad I did!!
Wonderful! Glad you liked it, Gary!
Brilliant, just what I needed. Thanks.
Excellent tutorial Leila. Thank you very much for your time and effort!
My pleasure, Iván! Glad you like it.
Excellent explanation Leila thank you!!!!
I wouldn't had eliminated the "null" at the end, I would've replaced them with "0" and keep the data in order to show changes on sales of the articles upon the time!
Nice job Leila! Very helpful
This video was released on my Birthday :)
Watched it today and it worked.
Soo excited. Thanks a lot. I was searching for such data clean and transpose.
Excelente aula, parabéns!!
This is the best video on this topic that I watched. Thank you so much for your sharing!
Wow, thank you!
Nice one! Thanks for sharing your insights!!
Hi,
This video is amaazzzingg!!
Solved my long drawn problem quickly!! Thank you! Bless you!
Thank you Leila, this video is the end for my whole day struggle.
data management made simple. Power querry is a game changer. Leila you are a star
Thank you so much, exactly what I was looking for!
Thank you! You're a life-saver!
Awesome trick, thanks a ton Leila.
Thank you for creating these videos they are super helpful
Thanks a lot for the video.This saved my 2 days of work.
I wish I could hit that like-button more than once. Thanks a lot for sharing your knowledge!! 🙌🏽🙌🏽
So nice of you, Samuel :)
Excellent video! Thanks.
Absolutely great! Looking forward to reach this level in the course I already subscribed at the date when it went live.
Awesome, thank you!
thanks a lot Ms Leila it is such a help watching your VLOG
This was exactly what I needed. Thanks!
Thanks! great walkthrough :)
Thank you - for sharing useful tips on Power Query.
Your videos are the best I ever watched !!! Thank you for sharing your knowledge :)
Wow, thank you!
Great video!! Been wondering what to do with my two-level columns - great solution this!
Brilliant stuff. This will save hours of work.
I can't believe all these treasures were here in UA-cam and I didn't notice !!! Good that i found 😀 highly recommend to anyone who wants to advance in data analysis 😉
Great explanation and example, thak's a lot Leila (Greetins from México)
maaaan... I could not ever imagine myself 15 years ago spending my leisure time exploring the all mighty EXCEL .. thanx for your amazing videos
Awesome!!! It's as if you were reading my mind. I was looking for a tutorial similar to this once since beginning of this week and here you are publishing it today. Thank you very much!
Glad I could help!
Amazing, thanks for the help!
GOD!!! You help me a LOT!!! with this video... many many thanks
Wonderful, I know more on excel thanks to the great Leila!
Great to hear!
Thank you so much. This is what I've looking for. I'm working for more than 10k rows of data that has been generated from analysis. My work is to unpivot the data so that it can be use for pivoting. Good thing I bump into your video and I completed the task.
PS. I don't have any knowledge of power query just a basic excel knowledge
Saved my life today. Couldn't thank you enough!
This video saved me HOURS of effort and got me tons of kudos at work - thank you for taking the time to put together these videos that are both reasonably short and eminently practical! Knowing excel has been a major game-changer for my career and it's content creators like you and Mynda Treacy who have gotten me to where I am. Thank you!
Wow, that's so great to hear! Thank you for the kind feedback!
Very helpful and informative!
clear and helpful, thx!
Thank you. Great lesson
Thank you, Leila!
This is rly helpful and clear, thanks a lot!
I was wondering how this could be done. I'm glad I googled instead of trying it myself. Your idea is brilliant and works and I wouldn't have come up with the sequence of steps myself!!!!
Glad I could help, Robert!
Thank you. This saved a lot of work for me.
Thank you this works great!
Great insight I didn't realise that you could do that. I'm going to have to spend some time learning unpivot, another really useful video.
Thanks for this great video Leila :)
The course is so intresting and its very easy with examples workouts. I give five ⭐ for this course. Thanks for sharing knowledge.
Wow, thank! That's great to hear.
Great technique! Thanks, Leila. I have not encountered this situation, but I’m sure I will. Now, I’ll be ready!
Always good to be prepared :)
Thanks for posting. Its really awesome.
👌 👏 👍
Thank you a lot for the tutorial! You help me to solve the task
amazing!!!! you are so amazing. you just saved like hours of stupid things i could've tried to clean up my data with multiple attributes. thanks so much!
OMG. I love all your videos. IDK what job that will let me work in excel and do cool stuff like this.
Hi Leila!Great Tutorial Using UNPIVOT,Love The Tip Using Centre Across Selection When Preparing The Data Ready To Upload For Power Query To Work It's Magic..Thank You :)
Glad you liked the tip :)
ooooooooo... this was a nightmare for so many years!! hours and hour unpivoting survey data!! thank you so much!! great work!
Hope it will come in handy for you Alexander.
Very useful video. Thank you
I never could find a use for remove table headers... until today. All around great insight, Leila!
You're welcome!
Thank you, very helpful! :)
PQ is the best for this! Great vid!
exCTLY WHAT I NEEDED. THXXX!
Happy learning !!! Thank you Leila
Thank you! 😃
thank you, soooo helpful!