Excel Unpivot Data with Multiple Headers (Multiple Row levels into Columns with Power Query)
HTML-код
- Опубликовано: 26 июн 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 !!
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.
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.
maaaan... I could not ever imagine myself 15 years ago spending my leisure time exploring the all mighty EXCEL .. thanx for your amazing videos
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!
You are the best, Always! Thank you for uploading great video ❤️
Hi 오빠두, I am your big fan:):)
I am halfway through your course and it has paid for itself in one day in time savings!
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.
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.
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
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!
Amazing, thank you. I have been struggling with Multiple Headers, it has given me a breath of fresh air. Thank you again.
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.
😘
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. 👍👍
Thank you! Spent the entire day untangling a large table. This video made the same thing possible in 15 minutes!
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
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!
Thank you very much Leila! This is definitely a life saving function!
THANK YOU! This video saved my sanity! I am fairly new to Power Query and this was extremely easy to follow and understand!
Thanks Leila, this answered all my questions about multiple headers in a table
This was an absolute godsend! Thank you!
This blew my mind. Thank you!!
This is amazing! From presentation skills to content!! Thank you!
Thank you for always coming in clutch ahead of my deadlines lol We love an Excel Queen!
Excelente aula, parabéns!!
It gives me my feed of the day about the Power query and appreciate your detailed explanation. I encourage you to upload more videos
Brilliant, just what I needed. Thanks.
As always, you have the perfect video to a niche question. Thank you!
Thank you! You're a life-saver!
Clear, concise, High Quality material. Thank you for this
Great one Leila. Thank you for sharing your knowledge.
Amazing Leila, these complex situations really needed to learn about
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 😊
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!
This video was released on my Birthday :)
Watched it today and it worked.
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!
This is brilliant!!! Not sure, how can anyone dislike this video? May be they didn't know about Excel ;)
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!
Thank you so much, exactly what I was looking for!
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
data management made simple. Power querry is a game changer. Leila you are a star
Excellent video! Thanks.
It's awesome what we could learn with you Leila
Thanks a lot
Soo excited. Thanks a lot. I was searching for such data clean and transpose.
Brilliant video. Every action beautifully explained. Thank you Leila
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!!
Thanks a ton for such a beautiful and simple explanation. This is really helpful.
Thank you this works great!
PQ is the best for this! Great vid!
Thanks! great walkthrough :)
Nice one! Thanks for sharing your insights!!
Amazing, thanks for the help!
Thank you. Great lesson
GOD!!! You help me a LOT!!! with this video... many many thanks
You are simply super! I really appreciate your efforts in sharing the knowledge to all!
Thank you, Leila!
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.
Awesome trick, thanks a ton Leila.
Thank you for creating these videos they are super helpful
Thank you Leila, this video is the end for my whole day struggle.
Absolutely great! Looking forward to reach this level in the course I already subscribed at the date when it went live.
Awesome, thank you!
OMG. I love all your videos. IDK what job that will let me work in excel and do cool stuff like this.
Excellent tutorial Leila. Thank you very much for your time and effort!
My pleasure, Iván! Glad you like it.
I can't believe all these treasures were here in RUclips and I didn't notice !!! Good that i found 😀 highly recommend to anyone who wants to advance in data analysis 😉
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!
Nice job Leila! Very helpful
Thanks a lot for the video.This saved my 2 days of work.
Great video!! Been wondering what to do with my two-level columns - great solution this!
Brilliant stuff. This will save hours of work.
clear and helpful, thx!
Hi,
This video is amaazzzingg!!
Solved my long drawn problem quickly!! Thank you! Bless you!
This was exactly what I needed. Thanks!
Thank you. This saved a lot of work for me.
exCTLY WHAT I NEEDED. THXXX!
Very helpful and informative!
Thank you, very helpful! :)
This is the best video on this topic that I watched. Thank you so much for your sharing!
Wow, thank you!
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!
Amazing and reusable as and when required.
Saved my life today. Couldn't thank you enough!
Great explanation and example, thak's a lot Leila (Greetins from México)
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.
I wish I could hit that like-button more than once. Thanks a lot for sharing your knowledge!! 🙌🏽🙌🏽
So nice of you, Samuel :)
Wonderful, I know more on excel thanks to the great Leila!
Great to hear!
Thank you - for sharing useful tips on Power Query.
Thanks for posting. Its really awesome.
👌 👏 👍
Thank you for sharing.
Thank you a lot for the tutorial! You help me to solve the task
I never could find a use for remove table headers... until today. All around great insight, Leila!
You're welcome!
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 :)
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!
This is rly helpful and clear, thanks a lot!
Honestly PQ blows my mind more every day. I had a report with multiple headers I naively believed I just couldn’t work with without changing the source table. With this I was able to quickly unpivot like a pro.
It really is a miracle tool 😊
You are such a good trainer.. I hope you are patient in real life too like this. I will even learn algebra in klingon
Happy learning !!! Thank you Leila
Thank you! 😃
very useful, I struggled with this problem a few weeks ago
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!
You are like god in Excel.