Combine complex data: Transpose - Unpivot - Pivot columns
ฝัง
- เผยแพร่เมื่อ 15 ก.ย. 2024
- 🚀 Ready to tackle your most complex data challenges? In this advanced Power Query tutorial, we’re diving deep into the world of messy datasets. Learn how to combine data from multiple Excel worksheets with issues like double column headers, data trapped in single cells, and random null columns. 🧩 No challenge is too tough when you have the right tools and techniques! 🔧
🌟 Don’t miss out on mastering these advanced skills! Hit play to transform chaotic data into a clean, actionable dataset. Like, comment, and subscribe for more pro-level Excel tips! 📊
--------------------------------------------------------------------------------------------------------------------------------------------------
🙏 Support my channel - Simply BuyMeACoffee☕
www.buymeacoff...
--------------------------------------------------------------------------------------------------------------------------------------------------
Download File
1drv.ms/f/s!Ar...
--------------------------------------------------------------------------------------------------------------------------------------------------
PREPARATION:
Data Analytics Road-Map: • How to become data ana...
Data analyst - Theory & Case Study: • Data Analysis Project ...
FULL PLAYLIST: Data analysis - Power Excel • Data Analysis Project ...
PART I - DATA TRANSFORMATION
1 - Dynamic Array VSTACK: • Combine data from mult...
2 - Power Query Basic: • Combine data with diff...
3 - Power Query Intermediate: • Combine multiple files...
4 - Power Query Advanced: • Combine complex data: ...
PART II - DATA ANALYSIS - Coming soon
PART III- DATA VISUALISATION - Coming soon
--------------------------------------------------------------------------------------------------------------------------------------------------
Timeline
===== INTRODUCTION
00:31 About the channel
00:59 Structure
===== DATA TRANSFORMATION
02:42 Data definition
04:01 - Import Data
06:47 - Transformation Strategy
08:02 Advanced Data Transformation
09:19 - Transpose
11:40 - Unpivot Table
13:37 - Pivot Table
15:24 Create Custom Function
17:20 - Helper Query
23:00 - Main Query
24:56 - Automation
===== CONCLUSIONS
28:36 Wrap-up & What's next
![HIWWHEE | OHM [Official MV]](http://i.ytimg.com/vi/OJJLtTLqLag/mqdefault.jpg)
This is one of the best videos on TH-cam for explaining this. Great work.
thank you for your kind words 🙏😊
i’m so thankful I found you as my teacher! 😊
great tutorial! congratulations! 🎉
As always, you are welcome! 🤗
Insanely useful, thank you 🙏
You're very welcome!
Hi Linh I just ❤ it. Please keep up this advanced data cleaning series in Power Query M.
Thank you, more to come 😉
What a Great and Extremely informative video, Thank you so much I look forward to more videos from you.
you're welcome 😊
I'm making the transition from VBA to PowerQuery and this video is of tremendous value! Thank you for sharing.
Glad it was helpful! 😆
And yes you'll find it's much easier to work with PQ 👍 than VBA when it comes to manipulating data.
OUTSTANDING!
Thank you !☺
Fantastic Linh 👏
Thank you 😊
Very nice explanation, but why not creating a function out of transformation steps, load the whole, folder, select the right files and run the function?
Thank you for the question.
Short answer is: if I do that, it would be a 3 hours long video and no one will watch as it's too technical 😅
Plus, the series aim to show how PQ works with different type objects (this one is about working with non-defined object in excel, that is simply any data within the worksheet).
From the technical POV: Yes, you are right. It's possible.
I can get data from Folder instead of File. Meaning, i'm now working with object: File.
Then I would need to do 2 loops: Folder level (to get multiple files), then File level (to get multiple sheets).
And you can totally code it with M-Code 😉
Great video!
thank you 😊
Thank you for the tutorial! Keep it up, You are doing an amazing job!😉
I'm glad you found the video useful 🙂 Cheers!
Nice job Linh
Thank you :-)
Gracias por tus aportes.
My pleasure 😉 Enjoy!
I was completely lost despite being IT literate and wondered the presenter should indicate what level this is pitched at in both the descriptor and intro,. I don't use PQ much . The sections on parameters meant nothing and no definition was given. Sorry for sounding critical but if you are to get subscribers it needs to better explained- why would a time sheet not be set up in the first place in one sheet rather than this odd example. Also the resolution was impossible to read :(
Thank you for your feedback.
This video is a part of the data analytic series. You can find the link to the playlist here.
th-cam.com/play/PLsQV_12wOPkHLDJgKd1SuAVMKPALM7PQf.html&si=nTTKoRvur7DRBt7f
You might want to start from the beginning as we build up knowledge from basic to advanced through out the entire series.
FYI, you can adjust the quality of the video in the setting to HD-1080p.
For more information, you can check out the description in description box below each video.
Timesheet data is set-up this way for teaching purpose.
ofc as you pointed out, no one should do like this in reality and that's why we will cover automation in the future videos.
@@LinhVo_HM appreciate you took the time and effort to reply - a first on youtube!....I will review your previous videos but do think that clarifying a term is important such as parameters in excel would remind if not reinforce learners ....it totally put me off :(
@@GeorgyGoogle Hi Gregory, My pleasure to reply and help.
The concept has already been explained in detail in the previous video - nbr 3. th-cam.com/video/YpnmNDUNxSw/w-d-xo.htmlsi=_tw4SZeGm_cJKFEY
I didn't want to repeat all those information in this video (it's already quite long, and very advanced).
I suggest you check out the whole series for more context. Let me know if it makes more sense.