MSPTDA 06: Power Query: Merge, Append, & UnPivot - 3 Important Transformations
Вставка
- Опубліковано 4 жов 2024
- Download Excel START Files:
people.highlin...
people.highlin...
people.highlin...
Download Excel FINISHED Files:
people.highlin...
Download pdf Notes about Power Query: people.highlin...
Assigned Homework:
Download Excel File with Homework:
Assigned Homework:
Download Excel File with Homework: people.highlin...
Download Files to import for Merge Homework:
people.highlin...
people.highlin...
Example of Finished Homework:
people.highlin...
In this Video learn about three important Power Query Transformations: Merge, Append and UnPivot.
Topics:
1. (00:15) Introduction.
2. (02:00) Example1: Merge 2 Tables to Replace VLOOKUP or Relationship
3. (08:11) Example 2: Transform 3 Non-Uniform Sales Tables Then Append into Single Proper Data Set
4. (16:31) Example 3: UnPivot to Convert Cross Tabulated Data Tables into Proper Data Set
5. (22:29) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.
The Power Query logo used in this video is copyright of and used with the express permission of powerquery.tra...
Thanks to Ken Puls and Miguel Escobar for letting me use their logo!!!!
Who else agrees this is the best Power Query Training ever😊
My company was laying people off today, and although there is nothing guaranteeing my future employment, I felt grounded, because I've been studying under you for the last 2-3 years; and developed skills which could give me options in job markets should I need them. I owe so much of that to you and your teaching. Thank you Mike.
You are welcome! That is EXACTLY why I post. I love to hear that your new skills may help you to avoid a layoff : ) You can give thanks on each vid with these comments and Thumbs Ups !!! Thanks for the support, Nick!
I was given a task to append over 4000 tables this week at work. My boss suggested I use a faster method if I know any. I was able to apply what I have learnt just watching few videos from you. It made me so excited and saved me the whole week trying to copy and paste table after table. Thank you Mike!
80% of my excel skills come from your videos. now i am passionate about excel and data analysis.
I am glad that the videos I post help you out!!!!! You have a great Excel Channel too - hopefully people will go and check your channel out: ua-cam.com/channels/qQO1-7JxERnT6o1iKjXXzw.html
You are totally right mate
Another fantastic video. Amazed at how easy unpivoting is with PQ and that trick of adding a column by selecting and multiplying existing columns blew my mind. Excellent homework questions to test how well it was all retained - am absolutely loving this series.
Yes!!!!!! I love that you find value in these lessons. When I use these at Highline College in my classes, I most get grief from students because they say it is too much work to learn these important tools... : (
Mike I`am stuck in lockdown & have followed you for a couple of years now, the power query module I have studued hard as plenty of free time & have brought this into the work place your BRILLIANT thanks keep the great work I love it ALL & will continue learning from YOU.
Glad to help during the lockdown and before! Thanks for your support with those comments, thumbs ups and of course your Sub : )
My pervious company was using older versions of excel and i really wanted to learn Power Query, Power BI and practice as well so, i switched my job only i can get access to MS office 365 and now i can finally learn all these skills. 🤣 By the way i have been watching your videos for last 7 years now and i have learned A LOT from you. My skills are also appreciated wherever i have worked so far and i owe it all to you. I am gonna watch all these videos in hopefully few days time. I also practice with the files you provide so i really appreciate you from the bottom of my heart. Thanks for sharing your knowledge for free.
I am glad that I have been able to help over the many years : ) : )
Dear Mike,
I would like to take this opportunity to thank you for all videos you have posted, especially on power query & data model.
Today, I had a challenging task from management and it was impossible to achieve w/o power query.
Thanks for making me the person I’m today..!!!
You are welcome for the help! That is why I post - to make the world a better place and teach all who want to learn how to have fun AND Efficiency with MS Tools. I love hearing stories like yours!!! I need your help, though, please support with Thumbs Up on every video that you watch, comments when you are inspired, a Sub and tell all your friends.
Add column from number! I like that! Thank you for a great video.
Yes, that is a useful feature : ) Thanks for your support, Teammate Leila!!!!
Quite unexpected to find you here
Professor Mike, as always thank you very much. I am bingeing on your MSPTDA power course and it's beyond brilliant. No word can describe how precious the gems you 'merged' and 'apended' over these 23 mins. It has transformed my life and I'm sure it did others' too. Thank you!!
I love to read your comment : ) I am very happy to help : )
No lies. It saved me this week when I was given thousands of tables to append.
The Power Query logo used in this video is copyright of and used with the express permission of powerquery.training
Thanks to Ken Puls and Miguel Escobar for letting me use their logo!!!!
I can never thank you enough as i have really learnt so much from your videos. Keep up the good work.
Glad it all helps, Joel!!
It's cool that they are still working on Power Query and adding new features that make life easier
like Append three or more tables
Yes, they keep adding new features : ) That is good : )
Yes, it is great becasue MS adds lots of useful features : ) Thanks for the support : )
Mike, como foi comentado a baixo, você é o cara, já acompanho os seus vídeos há anos, você é muito bom e tem uma didática muito boa, obrigado por tudo....Sem palavras...
Google Translator: Mike, as commented below, you are the guy, I have watched your videos for years, you are very good and have a very good didactics, thanks for everything .... Without words..
Você é bem vindo! E obrigado por ajudar em cada vídeo com suporte:) Os vídeos continuarão chegando:)
You are welcome! And thank you for helping out on each video with support : ) The videos will keep coming : )
I'm really appreciate for these training videos. I've learned su much from them. Especiallay MSPTDA section is priceless and you give us them without any payoff. Respect !!!
Glad the resources and MSPTDA help you, Alpay!!! They do have a price, though: you must click thumbs up and comment on each video : )
@@excelisfun
Sure, it's a pleasure for me
@@vacilando86 In this way we are a Team!!!!!
U are the best bro
I have discovered your channel several days ago and now I watch only u
U don't speak too fast and u try to explain everything
Don't stop
Thank u
I won't stop! You don't syopp learning either, and I love the comments and thumbs ups : )
What a great instructional video. These three transformations alone have saved me countless hours rearranging data in Standard Excel. Merge to return multiple matches, Append to attach many workbooks, and Unpivot Other Columns to transform a cross-tabular data set into a proper one. Thank you for the clear explanation and great video!
Append is such a transformative tool which for a user which will save a lot of time. Unpivot was really first time introduction for me and it cleared basics about original raw data format which then is transformed using pivot. How easy is it to unpivot using power query. Thanks Mike. God bless you.
You are welcome!!! #6 and counting : )
I love your tutorials as they are complete packages in their own right. It is brilliant that you can download the files for any individual topic you wish to learn and follow along and your passion and enthusiasm for teaching comes across really well. Yours are easily the best tutorial videos by far anywhere on the entire internet.
Yes, my intent when I started this channel 11 years ago was to provide free education to the world, with video, files, free book (pdf notes), practice problems and then try to tell a video story that can inform and inspire. I am glad that the resources help you. Please help my excelisfun channel reach more people in the world in a simple way; Sub, and then leave a thumbs up and comment on each video that you watch : ) Thanks, Pravin!!!
Thank you, Mike! I'm always looking for your videos!. You share your professional advice on what is the best practice of doing things, such as name a query first, change columns formats last. I believe knowing such steps and their best order saved me a lot of time and pain during learning new things. It's great to learn this first from you and later gain more confidence and shuffle them when needed. Was interesting to learn that the order of clicking on columns before expanding other columns will result in their order on the next step. Thank you, Mike!
That funny thing is, Vasilisa, always naming things is one of the most basic efficiency skills, that when done consistency leads to accuracy and easy of use. Most teachers and bloggers and UA-cam Excel people, including many of the greats, don't both to include it in their books, videos, articles and such. I always see that and just cringe. When you take a class from me, I try to always do the little things, and the big things in a smart way.
Thank you very much Mike! I just can't repeat it enough!
Once on each video is enough : ) I love it!
unpivot lecture is pure gold
Glad you like the gold : ) And thank you for the support with your comment, Thumbs Up and Sub, doug : )
Awesome video Mike, I didn't know how to keep my output data set on a specific sheet and avoid recreating a new one from the query ribbon, the right click update was exactly what I needed, thank you so much !!!
Mike your videos are so helpful that it helped me to have drastic changes in the office work so every day want to say you THANKS. Love from Pakistan
You are welcome, Amer!!!!
I am an addict to your channel!! Thank you for the amazing things you teach!!
You are welcome for the amazing addiction! Thanks for your support, Zenia, with your comment, thumbs up and your Sub : )
Simply awesome and highly recommended! The feature of merging fitted exactly into an assignment and the solution was a lot prettier and better! Really enjoy your videos. Thanks a lot!
You are welcome! What assignment? Assignment at work? Or, Are you taking a class somewhere, where Power Query is being taught and required for assignments? What was the assignment?
Hi Mike. It is an assignment at work in order to combine two different worksheets from two different external sources and create an XML file in a specific format which will be loaded into the software I work with. It has been quite interesting as I am not a programmer, but had to think about a lot of steps in this solution as my colleagues must be able to perform the same job. Love your teaching style. Your enthusiasm is a great example for all to teach others.
Thank you for your reply, Lars! I am glad that the videos can help with work : ) Thanks also for the kind words and for your regular support : )
Seriously. Your channel is just unbelievable. THANK YOU SO MUCH!!
You got it! I post so you and others can learn, have fun and support with awesome comments and thumbs ups like yours, 1877Pegasus!!!!
The coolest thing is that not only do you share your knowledge, but especially your files to follow step by step. If there are misspellings, I am self-taught in English
I am sure there are some mistakes, like misspelling because i do not have an editor to help - it is just me in one office making all the things that go into this video. If you find misspellings, let me know : )
I talk about myself; my poor english, not you
I am really enjoying and learning from this class series. Each video moves my understanding a little further down the field. Thanks Mike again and again :)
You are welcome, Peter, much more further to go down the field! Soon, we will all be outstanding in our field : )
You are welcome again and again for the path further down the field - so we will be outstanding in our field : ) Thanks for the support!!!!
Your Explanation is very clear and to the point, I Like this
Glad it helps!
Thank you so much! Your channel is my favorite channel of all time! very informative and very helpful for my work!
Nhat Truong really？
Seriously, This free playlist is better than many paid courses, I have tried many paid courses, but I have never ever understood the concepts like your learning style. I will make a refund because of you :). haha
Thank you, sir
I agree. And that is why I do not know why there are so few views and subscribers to my content. The story in each video is created so that anyone can learn the hard concepts, and it is free. And yet, so few views and subs. I have do everything that I know with marketing... But I have never figured about how to get this free content out to more people... BUT: The great news is that you, M. Alomery found it!!!!
Your videos are making it easier to analyze and manage my business. Thanks Mike
Another great video Mike! Thanks so much! There is so much information, I am starting to watch them all again to make certain I understand all the content. Looking forward to future videos!
Yes, don't forget the pdf notes too - such much free info between video, pdf, Excel file and practice problems. Thanks for supporting this channel, Scott! This series will run for about nine months with one or two video a week.
Just great! Amazing! And there´s one thing I like to add.... I understand everything you say, even with my English not that fluent. Thanks a lot!
You are welcome a lot, Clovis!!!!
Falling in love with Power Query thanks to your amazing videos Mike!
Most underrated channel on UA-cam ! Thanks
Great video! I am sure many more to come... especially looking forward to Group By, Parameter, Create Function, Invoke Custom Function. Surely lots of fun in store! Thank you.
O yes, so much more fun to come!!!! Thanks for your support, Sandip!!!!
As usual you are so clear and a great teacher!! Thank you
You are welcome! Thanks for the kind words, and thanks for the support with your comment, Thumbs Up and Sub : )
Once more a great tutorial Mike. The unpivot function is a pure releave for all who had to do that manually. There is one example I have prepared to demonstrate how to unpivot a cross-tabular table having multiple header rows per column. If fine for you, I will mail it to your gmail account. Awaiting your thumbs up or down. Thank you Mike.
No suggestions this time Mike. Only one short remark: E X C E L L E N T !!
Thanks Bart, but did you really mean EXCELlent?
yes just another one word... Wow!!!!!
Outstanding and maybe the most useful trasformations! thank you once again Mike!
You are welcome, Evgenia!!! Thank you for your support : )
Awesome as usual! I am wrapping up the Functions we are using in your playlist to come up with a list we used during this amazing training classes.
I got confused with regards to mechanism/usage for (Merge v.s. Vlookup v.s relationships) - hopefully by next videos that clarity would be elaborated as you said :)
GOOD STUFF! I like being able to change the column name without an additional step. 👍
Go Team!!!!!
Go Team!!!!! : ) : )
Always something new to learn or to brush up on it :-) thanks a lot mike you contribute a lot to our professional skills in excel :-)
You are welcome! I am happy to contribute to your Excel Skills!!! Go Team : )
3 Important Transformations everybody should know! Thanks for the video, it was obviously awesome! :)
Thanks Teammate, Malina : ) : )
Thank you, Mike. Relearning video for me today. :)
Many many thanks Mike ..you are the best
Just trying to be a good Teammate. Thanks for your support, Wajdi, and many you are welcomes : )
This is an amazing channel for Excel. Almost everything covered. I have been liking the videos so far. There are some queries to which I at times don't answer. Please let me know where can I put up those questions.
A big Thumbs up 👍
Excellent PQ tutorial. Thanks!
Thanks so much Mike! You are simply "AMAZING"! Thanks again!
Thanks Professor Mike
You are welcome!!
This is incredible. Thanks for the lesson!
You are welcome for the lesson. It is partially incredible because of the consistent support form viewers like you with your comments, Thumbs Ups and Subscriptions : ) Thanks, siaozam!!!!
This is really a great session. Thank you.
You are welcome, Md Islam!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Thanks for the amazing examples. 👍
You are welcome, Lalit : )
all lessons are "super", excellent
Glad you like them : )
Excellent tuition, sir. Many thanks.
Glad it was EXCELlent for you, Ian : )
Thanks Mike! Such great content as always! Big thumbs up
You are welcome, William!!!
This Power Query is a real Power. Thanks Mike. I never stop learning from you. 👍 And of course a Happy New Year 2020.. 🌟 🌟 🌟
Happy New Year, K D Salim!!!!
What a great video. Thank you Mike
Thank you very much again for sharing your knowledge... Cant ask for more.
You are welcome, Asantha!!!! Thanks for your support : )
Excellent explanation
Glad it helps!
Mike, you are 100% the best dude !
Thanks, Albert! Thanks also for your support with your comment, Thumbs Up and Sub : )
I am Power BI noob and DB Noob. My 2 cents on UnPivot is that the original table was normalized (no duplication, hard to analyze), UnPivot Un Normalized the Data and created a table that can be analyzed. In the new Table there is a redundancy but that is OK because each student has several data point. Each line is a new data point for a student.
Thank you sooooo much Mike for this awesome video.
Espectacular
Wonderful...simply awesome!
Thank You
You are welcome, Gagandeep Saggu!!!! Thank you for the support with your comment, Thumbs Up and Sub : )
Great, homeworks are best practice, solved class 05 homework differently
So great to hear that you are being creative with your homework practice, NanoBanano!!! Thank you for your support on the excelisfun channel at UA-cam : )
@@excelisfun no sir Thank you,
in this vid homework I had to change type using locale since I use ddmmyy, you really learn when your run into trouble
@@dafeac I think you are right about running into trouble - it actually helps us to learn : )
Your videos are incredible. I thought I was getting advanced in Excel then I found I had so much to learn. Quick question. Is there a way to use Power Query to pull in the contents of a Word document? It seems weird that I can easily query a txt document but not Word.
Thank you very much for your videos, they are very engaging and informative.
I do not know how to import a Word Doc. Text Documnets ("txt" and "csv") have been around pretty much since the beginning of databases. It is the form they decided on to allow data to move from one system to another.
@@excelisfun Thank you for the quick reply.
@@qmwhitlock You are welcome !
Keep safe my friend. you are great.
Glad this helps!
Just finish watching this video, so I'm looking forward for the next one :)
it takes 25 - 50 hours to prepare one of these videos, and that is on top of my normal job. I can't wait for the next one either - I am working on it right now : )
I just want to tell you that I'm learning so much from your channel. All your videos are excellent and so easy to follow. I really appreciate the time you put to make them :)
Thanks, Doris. It means a lot to get support and hear that the content is useful : ) : )
the video was so much fun i don't know how to thank you i'm still waiting for more i'm starving to your videos Mr. Mike hhhhhh thank s again
You are welcome again, Ismail!!!! Thank you for your support : )
Great videos and easy to understand
Glad it is easy to understand! Thanks for your support, Sunkanna!!!
Very helpful! Thanks 🙏
Another excellent video. You really made it fun.
Glad it is fun and informative, raheel!!!
Enjoyed this class!
: ) : ) : )
Awesome content! One question - after you cleaned up the three sales tables (2017, 2018, 2019) and appended them together as one table, what happens if you'd like to append the fourth table. Will it automatically get transformed and have the same columns as the other three or will you have to do it manually?
If you start the append manual, then you add new table manually. If you know you will have more tables, you can use From Folder for external data or internally in Excel Workbook use Excel.CurrentWorkbook. These topics are covered in previous two class videos: ua-cam.com/play/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1.html
Thank you a lot for your videos. they are amazing..
You are welcome for the videos, Pollo_Ragg : ) Thanks for the support with comments and thumbs ups on each video that you learn from : )
Another fantastic video, thank you!
Very informative video. Thanks. 🙌🙌🙌
You are welcome! : )
rất hay, cảm ơn bạn, tôi đến từ Việt Nam
Toi rat vui khi ban thich video cua toi . Toi dang o My.
Excellent video, thanks!
You are welcome for the EXCELlent video, Chris : ) : )
Thanks so much. I appreciate it hugely.
You are hugely welcome, mohamed!!!
Thanks
Thank you very much for your kind super thanks!!!!! It will help me to keep making more videos : )
Great Videos!!! Thank you Mike
You are welcome, Yolanda!!! Thanks for your support : )
Superb, sir. Thanks for this video!
You are welcome, Gunabalan!!! Thanks for your support on each video with your comment and thumbs up, and of course your Sub : )
Token of Gratitude!
Thank you very much for your kind donation, Ankur!!!!
great video, thanks!
Thank you!
You are welcome, Pravin!!
Thumbs up, as always.
Thanks, Orca : )
Thanks , great tutorial.
You are welcome, ozan sen! Thanks for the support with your comment, Thumbs Up and Sub : )
Thank you very much
You are welcome for the MSPTDA #6 fun, Kishan : )
Excelent!!! As usual
Unpivoting Other Columns is not only faster but if you have a new file with a different class it'll unpivot those too; whereas if you select only the specified class columns and do Unpivot Columns it'll hard code only those number of columns and won't update with the new file with the different class.
Yes, great point! Thanks Teammate Sal A : )
Thanks Mike. Awesome Staff!!! :) :)
Glad you like it, John!!!!
Thanks!
Thank you so much for your donation, Becky : ) : ) : ) It is so rare when people click that Super Thanks, so when someone like you does click it: Bam! I can working even harder to make more videos : ) : )
@@excelisfun You're welcome Mike, and thanks for the great videos! I'm learning a ton.
@@snailbait This is definitely the class if you want to do Microsoft Data Analysis with all their great tools : )
Fantastic explanation! :)
Very usefull Thank you
You are very welcome, sadyaz64!!! Thank you for the support with your comment, Thumbs Up and Sub : )
Thanks for this great video Mike 👍
You are welcome, K B!!! Thanks for the awesome support : )
Thanks Mike, great vid.
You are welcome, Dave! Thanks for the support : )