MSPTDA 06: Power Query: Merge, Append, & UnPivot - 3 Important Transformations

[@elactecnology]

Who else agrees this is the best Power Query Training ever😊

[@nsanch0181]

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.

[@excelisfun]

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!

[@vipinsingh075]

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.

[@excelisfun]

I am glad that I have been able to help over the many years : ) : )

[@timmytesla9655]

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!

[@hollydunne2687]

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.

[@excelisfun]

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... : (

[@Dogpotter69]

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.

[@excelisfun]

Glad to help during the lockdown and before! Thanks for your support with those comments, thumbs ups and of course your Sub : )

[@ankursharma6157]

Token of Gratitude!

[@excelisfun]

Thank you very much for your kind donation, Ankur!!!!

[@cakirankale]

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.

[@excelisfun]

You are welcome!!! #6 and counting : )

[@arifzainal9317]

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!!

[@excelisfun]

I love to read your comment : ) I am very happy to help : )

[@timmytesla9655]

No lies. It saved me this week when I was given thousands of tables to append.

[@ahmedal-dossary4386]

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..!!!

[@excelisfun]

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.

[@thiernoibrahimadiallo7740]

80% of my excel skills come from your videos. now i am passionate about excel and data analysis.

[@excelisfun]

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: kzbin.info/door/qQO1-7JxERnT6o1iKjXXzw

[@HanifKhan-mg8vu]

You are totally right mate

[@dani_danyalin]

Your Explanation is very clear and to the point, I Like this

[@excelisfun]

Glad it helps!

[@shoeshines2121]

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!

[@excelisfun]

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!!!!

[@pravinshingadia7337]

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.

[@excelisfun]

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!!!

[@vacilando86]

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 !!!

[@excelisfun]

Glad the resources and MSPTDA help you, Alpay!!! They do have a price, though: you must click thumbs up and comment on each video : )

[@vacilando86]

@@excelisfun Sure, it's a pleasure for me

[@excelisfun]

@@vacilando86 In this way we are a Team!!!!!

[@snailbait]

Thanks!

[@excelisfun]

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 : ) : )

[@snailbait]

@@excelisfun You're welcome Mike, and thanks for the great videos! I'm learning a ton.

[@excelisfun]

@@snailbait This is definitely the class if you want to do Microsoft Data Analysis with all their great tools : )

[@vasilisa6128]

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!

[@excelisfun]

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 KZbin 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.

[@Luciano_mp]

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..

[@excelisfun]

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 : )

[@joelngige5776]

I can never thank you enough as i have really learnt so much from your videos. Keep up the good work.

[@excelisfun]

Glad it all helps, Joel!!

[@grzegorz2852]

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

[@excelisfun]

I won't stop! You don't syopp learning either, and I love the comments and thumbs ups : )

[@LeilaGharani]

Add column from number! I like that! Thank you for a great video.

[@excelisfun]

Yes, that is a useful feature : ) Thanks for your support, Teammate Leila!!!!

[@randomguy-jo1vq]

Quite unexpected to find you here

[@TeamGarlic]

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!

[@excelisfun]

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?

[@TeamGarlic]

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.

[@excelisfun]

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 : )

[@zenia462]

I am an addict to your channel!! Thank you for the amazing things you teach!!

[@excelisfun]

You are welcome for the amazing addiction! Thanks for your support, Zenia, with your comment, thumbs up and your Sub : )

[@ExceliAdam]

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

[@excelisfun3903]

Yes, they keep adding new features : ) That is good : )

[@excelisfun]

Yes, it is great becasue MS adds lots of useful features : ) Thanks for the support : )

[@amerfarooq1142]

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

[@excelisfun]

You are welcome, Amer!!!!

[@1877Pegasus]

Seriously. Your channel is just unbelievable. THANK YOU SO MUCH!!

[@excelisfun]

You got it! I post so you and others can learn, have fun and support with awesome comments and thumbs ups like yours, 1877Pegasus!!!!

[@sudharshanr4997]

Falling in love with Power Query thanks to your amazing videos Mike!

[@macintouch83]

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 !!!

[@petermyran4986]

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 :)

[@excelisfun3903]

You are welcome, Peter, much more further to go down the field! Soon, we will all be outstanding in our field : )

[@excelisfun]

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!!!!

[@patrickbanzon7145]

Thanks Professor Mike

[@excelisfun]

You are welcome!!

[@nhattruong7651]

Thank you so much! Your channel is my favorite channel of all time! very informative and very helpful for my work!

[@qingli7415]

Nhat Truong really？

[@thiernoibrahimadiallo7740]

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

[@excelisfun]

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 : )

[@thiernoibrahimadiallo7740]

I talk about myself; my poor english, not you

[@AVINASHKUMAR-yi4od]

Excellent explanation

[@excelisfun]

Glad it helps!

[@scottgaines2677]

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!

[@excelisfun]

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.

[@871340730]

Thank you very much Mike! I just can't repeat it enough!

[@excelisfun]

Once on each video is enough : ) I love it!

[@mattschoular8844]

Your videos are making it easier to analyze and manage my business. Thanks Mike

[@dougmphilly]

unpivot lecture is pure gold

[@excelisfun]

Glad you like the gold : ) And thank you for the support with your comment, Thumbs Up and Sub, doug : )

[@ncnc2896]

As usual you are so clear and a great teacher!! Thank you

[@excelisfun]

You are welcome! Thanks for the kind words, and thanks for the support with your comment, Thumbs Up and Sub : )

[@sandip_bettereveryday]

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.

[@excelisfun]

O yes, so much more fun to come!!!! Thanks for your support, Sandip!!!!

[@cmaman1]

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

[@excelisfun]

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!!!!

[@lalitsharma9417]

Thanks for the amazing examples. 👍

[@excelisfun]

You are welcome, Lalit : )

[@evgeniam8882]

Outstanding and maybe the most useful trasformations! thank you once again Mike!

[@excelisfun]

You are welcome, Evgenia!!! Thank you for your support : )

[@clalgbarros]

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!

[@excelisfun]

You are welcome a lot, Clovis!!!!

[@mazarata73]

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.

[@mohamedchakroun4973]

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 :-)

[@excelisfun]

You are welcome! I am happy to contribute to your Excel Skills!!! Go Team : )

[@MalinaC]

3 Important Transformations everybody should know! Thanks for the video, it was obviously awesome! :)

[@excelisfun]

Thanks Teammate, Malina : ) : )

[@Abdelrahmankali]

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 :)

[@iankr]

Excellent tuition, sir. Many thanks.

[@excelisfun]

Glad it was EXCELlent for you, Ian : )

[@MrBlereau]

Most underrated channel on KZbin ! Thanks

[@sasavienne]

This Power Query is a real Power. Thanks Mike. I never stop learning from you. 👍 And of course a Happy New Year 2020.. 🌟 🌟 🌟

[@excelisfun]

Happy New Year, K D Salim!!!!

[@helenpyon9860]

Excellent PQ tutorial. Thanks!

[@barttitulaerexcelbart9400]

No suggestions this time Mike. Only one short remark: E X C E L L E N T !!

[@excelisfun]

Thanks Bart, but did you really mean EXCELlent?

[@fiazhitachi5732]

yes just another one word... Wow!!!!!

[@manfredzink6289]

all lessons are "super", excellent

[@excelisfun]

Glad you like them : )

[@MdIslam-zj6yt]

This is really a great session. Thank you.

[@excelisfun]

You are welcome, Md Islam!!! Thanks for the support with your comment, Thumbs Up and Sub : )

[@TheConanwarrior]

Thanks Mike! Such great content as always! Big thumbs up

[@excelisfun]

You are welcome, William!!!

[@PassportAndPixels]

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 👍

[@871340730]

Thanks so much Mike! You are simply "AMAZING"! Thanks again!

[@filipsvakjaroski20]

Thank you, Mike. Relearning video for me today. :)

[@siaozam]

This is incredible. Thanks for the lesson!

[@excelisfun]

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!!!!

[@wajdimatoussi]

Many many thanks Mike ..you are the best

[@excelisfun]

Just trying to be a good Teammate. Thanks for your support, Wajdi, and many you are welcomes : )

[@OzduSoleilDATA]

GOOD STUFF! I like being able to change the column name without an additional step. 👍

[@excelisfun3903]

Go Team!!!!!

[@excelisfun]

Go Team!!!!! : ) : )

[@hi2832]

Another excellent video. You really made it fun.

[@excelisfun]

Glad it is fun and informative, raheel!!!

[@nimanitto]

Keep safe my friend. you are great.

[@excelisfun]

Glad this helps!

[@hiteshjoshi3148]

Sir why didn't we appended the table by M function Excel.CurrentWorkbook() and filtered out other objects and tables in the power query please do reply i wont annoy you much after that.

[@sunkannamadigonde721]

Great videos and easy to understand

[@excelisfun]

Glad it is easy to understand! Thanks for your support, Sunkanna!!!

[@rodrigocustodio1955]

Mike, you are 100% the best dude !

[@excelisfun]

Thanks, Albert! Thanks also for your support with your comment, Thumbs Up and Sub : )

[@kosalanef]

Thank you very much again for sharing your knowledge... Cant ask for more.

[@excelisfun]

You are welcome, Asantha!!!! Thanks for your support : )

[@qmwhitlock]

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.

[@excelisfun]

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.

[@qmwhitlock]

@@excelisfun Thank you for the quick reply.

[@excelisfun]

@@qmwhitlock You are welcome !

[@kss2066]

Enjoyed this class!

[@excelisfun]

: ) : ) : )

[@elactecnology]

What a great video. Thank you Mike

[@himanshudalai1028]

Thank you sooooo much Mike for this awesome video.

[@FRANKWHITE1996]

Very informative video. Thanks. 🙌🙌🙌

[@excelisfun]

You are welcome! : )

[@Siberian_valenok]

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?

[@excelisfun]

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: kzbin.info/aero/PLrRPvpgDmw0ks5W7U5NmDCU2ydSnNZA_1

[@Mishkafofer]

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.

[@pollo_ragg5080]

Thank you a lot for your videos. they are amazing..

[@excelisfun]

You are welcome for the videos, Pollo_Ragg : ) Thanks for the support with comments and thumbs ups on each video that you learn from : )

[@dorissweanapo9860]

Just finish watching this video, so I'm looking forward for the next one :)

[@excelisfun]

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 : )

[@dorissweanapo9860]

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 :)

[@excelisfun]

Thanks, Doris. It means a lot to get support and hear that the content is useful : ) : )

[@evelynapelado1424]

Cannot download the sample datasets. It says can't reach this page whenever the link is clicked.

[@shubhampawar8506]

❤️

[@excelisfun]

Three, all in one : )

[@AnhTuan-fv2kn]

rất hay, cảm ơn bạn, tôi đến từ Việt Nam

[@excelisfun]

Toi rat vui khi ban thich video cua toi . Toi dang o My.

[@ingridquan6036]

I noticed that I can save it without the error if i close the workbook that has the queries, save the sales file and then re open the query one to refresh , but why would it do that

[@dafeac]

Great, homeworks are best practice, solved class 05 homework differently

[@excelisfun]

So great to hear that you are being creative with your homework practice, NanoBanano!!! Thank you for your support on the excelisfun channel at KZbin : )

[@dafeac]

@@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

[@excelisfun]

@@dafeac I think you are right about running into trouble - it actually helps us to learn : )

[@adamshrinah2595]

Thank you

[@excelisfun]

Yes!!!! SQRT(36) video and counting ; )

[@nishantkumar9570]

Thank you very much, Can we add custom row to any Table

[@excelisfun]

I do not know how to add a Custom Row...

[@nishantkumar9570]

ExcelIsFun There is a function in Power Query "InsertRows" i read about this on it's offical site but unable to make use of it. There are many other rows function as well. Couod you please have an eye on those?

[@nishantkumar9570]

ExcelIsFun i have a situation in which i need to add first date of month in first row of date column. And last date of the month as well . Although i get it in separate two columns for each row but i need to add this in the date column. Is there any way?

[@ismailismaili0071]

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

[@excelisfun]

You are welcome again, Ismail!!!! Thank you for your support : )

[@jimmys0307]

Wonderful...simply awesome! Thank You

[@excelisfun]

You are welcome, Gagandeep Saggu!!!! Thank you for the support with your comment, Thumbs Up and Sub : )

[@Claudemcd1]

A question about creating a date column in Excel power query from a year column plus a month column... the DATE function does not seem to work. What do I do wrong?

[@reng7777]

i got a question, how to set an automation for the UNPIVOT table( in power query), when the source table is adding new columns information every month. i would really appreciate your help with this question.

[@chriswebb4797]

Excellent video, thanks!

[@excelisfun]

You are welcome for the EXCELlent video, Chris : ) : )

[@jessieyuan8260]

Very helpful! Thanks 🙏

[@yolandavelasco1889]

Great Videos!!! Thank you Mike

[@excelisfun]

You are welcome, Yolanda!!! Thanks for your support : )

[@uo67113]

Thanks Mike, great video. I cannot find the txt file to do the merge exercise, where is it?

[@deesterdee6650]

Hi! I have a data model in PowerBI but am getting errors on some excel files because they contain extra columns. My client said some files have a couple more coilmns because they started collecting additional data over the past couple of years. How do I get the data model to match up the various columns that are in different positionsaand also allow for the additional columns to show up for the newer files? (Note: There is one excel file per week per year for 7+ years, so I don't want to have to do any manual edits to these excel files. Each Excel file has one table. All the tables are named the same, e.g. "VAorders".

[@svk853]

Hi Mike, another great video. Just wondering if you have a video that demonstrates the data duplication issues that can result from the "unpivot" feature? I've been struggling with this a lot recently.. many thanks

[@excelisfun]

I do not. I have not run into this issue. Can you send me a small simple example in an Excel Workbook file that can illustrate what you have encountered to excelisfun at gmail. Maybe I can make a video.

[@svk853]

@@excelisfun sure. Thank you Mike

[@romanguz]

Another fantastic video, thank you!

[@moethant5476]

Hello sir, I am not able to download the files. It shows "You don't have permission to access this resource.", pls help me. thank you very much

[@letsexcel8077]

Superb, sir. Thanks for this video!

[@excelisfun]

You are welcome, Gunabalan!!! Thanks for your support on each video with your comment and thumbs up, and of course your Sub : )

[@Sal_A]

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.

[@excelisfun]

Yes, great point! Thanks Teammate Sal A : )

[@majeedrehman5968]

Hi Mike I have downloaded the files that accompany this video. Unfortunately the text/csv file is not there or missing. Can you plse look into this. Thanks

[@excelisfun]

Nothing is missing. Click Show More button below video to see all files : )

[@mon_rich5366]

the best

[@excelisfun]

Glad to help!!!!