MSPTDA 04: Power Query: Import Multiple Excel Files & Combine (Append) into Proper Data Set

Download START Files:
people.highline.edu/mgirvin/A...
people.highline.edu/mgirvin/A...
people.highline.edu/mgirvin/A...
Download FINISHED File: people.highline.edu/mgirvin/A...
Download pdf Notes about Power Query to import Excel data: people.highline.edu/mgirvin/A...
Assigned Homework:
Download Word Document and read:
people.highline.edu/mgirvin/A...
Then download the rest of the files and complete the homework:
people.highline.edu/mgirvin/A...
Examples of Finished Homework:
people.highline.edu/mgirvin/A...
people.highline.edu/mgirvin/A...
Buy excelisfun products:
teespring.com/stores/excelisf...
In this Video learn how to import data from multiple Excel Workbook Files and append into a single Proper data Set.
Topics:
1. (00:12) Introduction
2. (02:18) Look at Data Import Files and the different objects that are in an Excel File
3. (06:56) Import Excel Files From Folder
4. (08:11) Look at Excel File in Power Query Editor
5. (08:26) Transform extensions to all lowercase
6. (08:34) Filter to include only Excel Files in import process
7. (09:10) Extract Excel File Name to create New Column for City. Split By Delimiter.
8. (10:01) Power Query Options: Don’t Change Data Type
9. (11:10) Rename Column and Remove unwanted columns
10. (11:34) Add Custom Column with Excel.Workbook Function (M Code Function). Explanation of what functions extracts from the Excel Files.
11. (15:14) Filter Out Excel Objects that do not meet Criteria = Sheet
12. (15:37) Filter out names that Do Not Begin With Sheet. Extract Worksheet Name to create New Column for SalesRep.
13. (16:08) Final Append to get all Excel Worksheet that contain Proper Data Sets with a proper SalesRep Name.
14. (17:41) Apply correct Data Types
15. (18:50) Load to Excel Sheet
16. (19:41) Change Default PivotTable Layout & Options
17. (21:19) Build PivotTable Report
18. (23:40) Definition of a PivotTable
19. (26:12) Add New Excel Workbook Files to the Folder & Refresh the Query and PivotTable
20. (29:35) Edit Query when Folder Path Changes
21. (30:57) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

[@excelisfun]

Homework practice problems are now listed below video. pdf notes are there too.

[@dorissweanapo9860]

is worth it to do the hw practice problems, so thank you Mike for posting them :)

[@excelisfun]

You are welcome! Practice makes perfect : )

[@azlanm0305]

Hai Mike, how are you? I am from Kuala Lumpur Malaysia.. First time writing to you.. Thank you very much for your guidance.. I have learn a lot and it helps significantly in my line of work as certified accountant. I developed my own accounting solution using most of the power query tools to transform business transactions data into automated journal entry and thus generating accounting ledger which automatically calculates account ledger balance and thus generating trial balance... Just my question is, how to assess on computer workstation specification in dealing with large business data Is there any ways of data file organisation OR better Power Query tools which has more efficient in terms of data processing or I should upgrade to i6 processor? If you are okay, can you shoot me an email at [e-mail] Then I can explain further the issues of data processing time Thank you very much Mike

[@excelisfun]

I do not know the answer to this question. You might try posting a question to this form: www.mrexcel.com/forum/index.php and click on BI section. Or search for Microsoft Power BI Forums.

[@azlanm0305]

Thanks mike

[@patrickbanzon7145]

Thank you so much, for the content Professor Girvin. I have studied E-DAB and am now at MSPTDA 04.

[@excelisfun]

Yes!!!! I am glad you will study both : )

[@shoeshines2121]

This was absolutely great! The visuals, homework problems, and pdfs to go along with videos are simply amazing! Thank you so much!

[@panh4190]

Thanks Mike for such an amazing video! 4 years passed by but it never gets old :)

[@excelisfun]

Even though the user interface is different in some of the MSPTDA classes, the foundation of data analysis with MS tools learned in this free class is still rock solid good four years on : )

[@__HumanBeing]

Remove columns and order them at the same time was the most impressive skill I saw in this video

[@excelisfun]

Glad it helped!!!

[@tableaupro3233]

Mike, I love the way you put the chapter markers - I could quickly scan them and know whether I need to watch any part or not. For this video, I just quickly went to minute 10:01 to check out the option about not changing data types and skipped the rest of the video as I knew all the other content. Very good time-saver.

[@williamarthur4801]

I've worked through this several times and love the use of Excel.Workbook and the extraction of what's needed. Simply brilliant.

[@excelisfun]

You are welcome, william!!!!

[@petermyran4986]

Another great one, Mike! I liked learning about the Query default settings (auto-change-type) and Pivot Table defaults. With each of these PQ adventures, I am feeling more comfortable, beginning to grasp what is going on. I feel I am at the cusp of the very familiar transition where a new technology doesn't just seem like voodoo, really working only for the gurus teaching it.

[@excelisfun]

Glad it is helping! Many more videos to come. Be sure to help support this free resource with those Thumbs Ups, Comments when you are inspired and tell all your friends : )

[@GeertDelmulle]

Majestic Mike, simply majestic, Mike! You are the Very Best -- bar NONE! :-) This series is compact and action-packed -- makes your the "Jack Bauer" of Excel learning channels here on youTube. It is very nice to see that you in your typical super-efficient way are integrating everything we've learned in the myriad of EMT videos on these subjects: just wonderful. Oh, and those visuals,... those visuals, OMG! They make it so easy to understand and follow the reasoning. Just perfect! In consequence: your power style for explaining things is so good that it makes watching other channels into an unbearably inefficient waste of time -- they're just not as action-packed as your videos. So in conclusion: to those wanting to learn these Power Tools: don't waste your time on other channels: you've got your one-stop-shop for Excel right here! Enjoy!

[@excelisfun]

Thanks for the very kind words about the content I make! Your support means a lot and helps me to keep making more fun visuals and all : )

[@hemanshumehta]

There are Series of Phrases given, In every phrase there is one color basis on color in phrase I have to lookup its assigned to whom basis on other table. how will i do this

[@brianxyz]

The last 13 minutes or so of the video is worth watching multiple times. Your definition of what a pivot table is makes what's happening under the covers so much more understandable. I don't think I've ever seen a pivot table described like that but it makes you appreciate that they are so much more than just summary reports.

[@excelisfun]

brianxyz, I have defined PivotTables like that for the past 10 years. In all my Highline Class I have posted over the last 10 years (Excel Basics, Highline Class, Statistics Class and more) I have defined it that way. If you are a student in my classes you get multiple quiz and test problems that ask the definition. The good news is, that as a teacher, I repeat, repeat, repeat all the import things many times, so viewers can eventually find the useful details about Excel somewhere among the 3000+ videos : ) The bad news is that PivotTables have been partially defined as Summary Reports for the past 30 years. One time about 15 years ago, I was in an interview and they asked the final question (that was supposed to trip up the potential candidate): "What is a PivotTable?" I of course answered in full detail: "Summary report that makes calculations with conditions or criteria with row and column criteria...." The interviewer looked at me like a confused dog and asked: "what do you mean calculations with criteria?" I looked back like an even more confused dog and explained to the interviewer that each cell was a separate calculation with multiple criteria... As you say, brianxyz: "pivot table described ... are so much more than just summary reports". Yes they are: they are THE great Data Analysis tool ever invented. BTW, Microsoft did not invent the PivotTable. It was Lotus 123 that introduced a separate tool called Lotus Improv created by lead programmer Pito Salas back in 1989.

[@brianxyz]

Just read up on Lotus Improv on Wikipedia. Very interesting history and a great example of bad marketing and/or not knowing how to market a product. Mike, did you ever use this product? What was the performance like? I like your interview story. Was the person impressed enough to give you the job?

[@rrrprogram8667]

ExcelIsFun good one mike

[@timmytesla9655]

I never thought of a pivot table like this! Your definition made me rethink. Thanks as usual Mike.

[@excelisfun]

You are welcome, Timmy!!!!!

[@aqeelhashmi6160]

You are absolute Excel Ninja & Guru... Really amazing videos. I would like to thank you for your kind efforts.

[@MrNinjagofan]

Thanks for this amazing video. I am literally using the entire MSPTDA playlist to educate myself on Power Tools. I also find very useful the homework files. I am doing them diligently :)

[@excelisfun]

That is why I post - to educate for free : ) For each video you learn from, just click thumbs up, leave a comment if you want, and tell all your friends about excelisfun : )

[@Whyvardhann]

I could spend a week learning excel from you and still continue to be amazed.

[@excelisfun]

Glad all the materials help, Yashvardhan!!! Thanks for your support : )

[@mazarata73]

Great tutorial Mike. This example of having several excel files in a folder and incrementally increase the number of files does indeed have a great relevance out there in the business. Sometimes ERPs produce these incremental files based on timestamp considerations. And thanks for the files, I followed each step along with you.

[@excelisfun]

Yes, providing the files to follow along and the practice homework problems is very important for learning : ) Glad the videos are great for you, Giuseppe! Thanks for the support : )

[@ismailismaili0071]

dear Mr. Mike to be honest you are unconditional in excel thank you so much.

[@excelisfun]

You are welcome so much, Ismail!

[@denisdwyer3103]

I have just completed the first four videos. You are doing a terrific job Mike.

[@excelisfun3903]

Glad you enjoy the class, Denis!!!

[@vikramreddy6732]

Mike, thank you for a fantastic tutorial. You hit all the topics I needed and I loved following your time-saving best practices.

[@excelisfun]

You are welcome, I am happy to help, Vikram!!!!

[@tomr9969]

Great 30 minutes time spent learning more about PowerQuery! This is a good refresher on how to work with multiple workbooks and appending into a single table. Must know concept for Excel experts! Thanks for putting this together; on to video MSPTDA05 :)

[@excelisfun]

You are welcome, Tom R!!! Thanks for your support : )

[@hollydunne2687]

Brilliant video and resources. Filtering the files to include in the folder path is a game changer! Literally hours of my working week have bene saved! Thank you.

[@excelisfun]

Yes!!!!! I love to hear that there are game changer tips for your work, Holly!!!

[@rrrprogram8667]

So, lately you are using many visual images for better understanding.... thats totally AWESOMEEEE

[@excelisfun]

Well... RRR, I have used many visuals in past videos too, but yes, for this series I want the communication to be as efficient as possible, so I will then have to use lots of efficiently fun visuals! Thank your for your consistent support : )

[@mihailela2267]

Thank you, Mike it's a very smart video of Power Query. This video could save me more than 5 h/week at my previous job (the slicers from the second part of the video are BRILLIANT ). Currently, I'm between the jobs but I'm happy to learn from one of the best teachers of Excel.

[@excelisfun]

Thank you for your kind words! The good news is that even though you are between jobs, your next boss in your next job can NOT wait to have you as an employee with your new Excel Skills!

[@gborka]

Thank you very much. God Bless. The course is beautiful. Now I'm in the middle of lecture 004. Very understandable lectures flow, Attached xls and summary in pdf make it very comprehensive.

[@excelisfun]

You are welcome for the full free course, Boris! Thanks for helping me with your comments and thumbs ups on each video and of course your Sub : )

[@linhnguyenba5418]

Thanks man. So loading from excel is an easy example. What would be more challenging is loading from text file and doing all the code to make it a table like excel. Inspired from your example, I have made my way to solve the situation I have. great video man.

[@sohandhiman2280]

You have cleared my concept thankyou so much to design video in such a beautiful way. Each and every point is crystal clear.

[@LeilaGharani]

Thank you for all the practice files! Great video on Power Query :)

[@excelisfun]

You are welcome for the files and Power Query Fun : )

[@azlanm0305]

Hai Leila, how are you? I am quite a fan of your videos and hoping that we can be collaborator between us I studies extensively on Microsoft Power Tools and am hoping that maybe I can refer to you on personal basis if you are okay pls shoot me an email at [e-mail] If you read my comment reply above to MIke, I am heavy user of Microsoft Power Query developing journal entry tables to generate accounting ledger for my work purpose The thing that I am yet to really explore is Microsoft Power BI and I have checked your videos and woww.. there is a lot to learn on presentation skills Anyway, thank you for your for videos .. it does enlightened me

[@manideepak6820]

Why you not added this technique in your Udemy course

[@1877Pegasus]

I have seen some of your videos and they all are amazing. I have read you want us to pay you back just with our comments and likes. I think you deserve so much more but at least, you will always have my comments. THANK YOU SO MUCH!!!

[@excelisfun]

Yes, 1877Pegasus, that is all I charge: a comment and thumbs up on each vid. That makes us a Team, i make and post and you can comment and thumbs up : )

[@juliamartinez6021]

dude, you are my hero!! I love your style of teaching. Thank you so much for the effort.

[@excelisfun]

Yes!!!!! We are a Team: I make and post, you watch, learn and comment & Thumbs Up : )

[@afzalzamiransari]

I watched several videos on power query but non of them clear my concept. Your video is indeed helpful. Thanks sir

[@excelisfun]

You are welcome, AFZAL!! I am glad that the resources that I post can help. Thanks for your support with your comment, thumbs up and your Sub : )

[@Abdelrahmankali]

Content explanation is awesome, I can't wait to try the exercise to apply same as a trial. Thank you Mike.

[@sequa74]

One of the best video to insert file name and tab name as columns in the database

[@mazarata73]

Guess what? I did skip the Netflix session and just followed the #03 tutorial. Thanks for that one too. Oh yes, another thing. I just got your book Ctrl+Shift+Enter this week. And am happy to have seen in this video, what you describe as the golden rule :-). Looking forward to more.

[@excelisfun]

Thanks for buying the book - it is s great formula book. Lots more fun to come! Thanks for your support, Giuseppe!

[@mehdihammadi6145]

Hello Mike, this episode is very interesting, I just learned one or two tips that were unknown to me. Thank you for sharing

[@mehdihammadi6145]

I just wanted to add this comment. Some of your followers, do not use an English version of Excel and because of this, some transformation steps cause errors (Text to Currency or Decimal) because, for example, the character used for the decimal is not the same (comma in French and point in English). I suggest you add a topic (or step) in the pdf to indicate how to set the "Query Regional Settings" to make Power Query recognize the source data format in the appropriate way.

[@enriquedominguez9709]

Thank you Mike, complete, clear and easy to follow explanation.

[@excelisfun3903]

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

[@nicolastalvasson2539]

Thanks a lot (from France) for this great video, and the incredible work you have done

[@fredzfranz09]

I think you just have to turn off the enable background refresh on the query connection. What it does is that it will refresh the query in the background and that means multitasking the other refresh like pivot when the query hasn't completely loaded all data to pass on to the pivot. This is completely helping me in my job. I am so grateful to Mike and his tutorials. I hope you help a lot more people. Just amazing!

[@excelisfun]

You are welcome, Red!!!! Thanks for your support with your comment, thumbs up and your Sub : )

[@TheSybolt]

Astonishingly well made video. Absolute summit!

[@excelisfun]

Glad you like it!

[@ockipoki]

I like the rule filtering at 15.14. I use this for error catching input files. I rely on multi-user generated input files so this helps make sure the data in is sound.

[@santoshpv321]

Top Gun!! Your teaching makes it so simple, so easy to follow...Thx a lot...

[@excelisfun]

Glad it helps and is simple : )

[@BillSzysz1]

Brilliant. This is the most comprehensive explanation on this topic I have ever seen. Thanks MASTER Mike!!!!

[@excelisfun]

You are welcome TRUE Poet Artist and Master of Power Query Bill Szysz !!!!! Hey, maybe you can answer the questions from Matt Schoular and Bart Titulaer below!?!?! I did not know the answer...

[@excelisfun]

BTW, I learned it all from you : ) So I am the one that must thank you : ) : ) : )

[@BillSzysz1]

We all learn from each other :-)) So, i also thank you :-))))

[@excelisfun]

Go Team!!!!

[@lisamccray6481]

I am as new to this stuff as they come. I only know what these videos teach. Other than that, consider me a soccer mom. My point is that at first I was gonna just thumbs up the comment. For me to do a double take and realize that I am about to give a thumbs up to a comment which happens to be from Bill Sycz, means I have been watching these videos. And now for the icing. These two men still. Still consider each other as brilliant. It was difficult to not become emotional for them and for myself for recognizing this as well. When it comes to this stuff, I may as well be from Australia, ( the land down under). 🙂. So I would imagine that it has that widespread effect on others. Much respect, Guys. Pooowwwer Quuueeerry. It used to feel like 👿. Now it feels like 😎. You are at the slicer part, Mike. 🤔 I gotta go. Gotta rewind.

[@geeee8933]

Wow, never knew (a) that I could updated the default PivotTable layout or (b) that Data Model PivotTables inherently avoid having to update the number format of output fields... amazing.

[@elactecnology]

Just as expected. This is just amazing. Everthing clearly explained!

[@excelisfun]

Glad there is clarity, Elcidio : )

[@artemasleslie]

Your videos are great, excellent pace, and very informative!

[@excelisfun]

Glad they help you, Artemas !!

[@Pat14922]

Hi Mike- nice video again. The documents and working files your provide are really helpful - thanks! Off to try the homework.

[@excelisfun]

Awesome! Yes, the homework is usually quite fun. Wait until you get to my favorite homework below video MSPTDA 8.5 : ) Thanks for your consistent support, Peter.

[@harifitfunk1112]

Amazing..... I am a newbie to excel and the videos are simple & gripping..... superb

[@excelisfun]

I am glad that they help, harifitfun!!!!

[@wajdimatoussi]

I become addicted to your videos ;) please do not stop!otherwise I will ... :p Many thanks Mike

[@excelisfun]

Thanks for watching, Wajdi!!! I won't stop if you keep click that Thumbs Up and leave on comment on each video because then we are both addicted : ) This series will continue for about nine months with one or two videos each week : )

[@sajidmuhammed1484]

You are awesome, as always! Please keep up doing humanity good :) be blessed.

[@amenaislam4573]

Thanks is not enough. Really appreciate your devoted hard work for people.

[@excelisfun]

You are welcome, Amena!!!

[@cynthiacotney7918]

🤯I'll have to watch this one again to grasp all that. Thanks💜

[@excelisfun]

That is what is so good about video teaching: the rewind button : ) Thanks for your consistent support and purple hearts!

[@user-ju1it8wf2j]

I was confused about what to do during the lock down in India because of COVID19. Now I started following this course and I think I can meaningfully spend the balance lock down period to learn so many new concepts. I really like your systematic approach and minute things like naming the sheets etc., Great man! I create Excel tutorials in my mother tongue Tamil and I really like your video editing style also. Zooming into the window wherever required!

[@excelisfun]

I am so glad that I can help during this diffident time in our world history. Thanks for your comment, thumbs up and of course your Sub : )

[@mikoajgordiiko8363]

Mike, thank you very much for so structured materials.

[@excelisfun]

You are welcome for the structured materials! Thanks for your support with comments and thumbs ups : )

[@mattschoular8844]

Great video Mike. Thanks for the tip on the pivot table tabular default settings....

[@excelisfun]

You are welcome, for the video and Default Settings for PivotTable! Thanks for the support : ) : )

[@sudharshanr4997]

Such passionate teaching! Thanks Mike :)

[@excelisfun]

You are welcome!

[@karljolivet5991]

I just love the function excel.workbook. Great video can’t wait for next week

[@excelisfun]

I love it too !!!! It takes so long top make a video with Excel files, pdf notes, homework and all... So it will be about 4 days. Thanks you for the support, Karl - it really helps in keeping to make videos : )

[@MalinaC]

And homework! Masterpiece :) Thank you, Mike!

[@excelisfun]

You are welcome, Teammate Malina : )

[@aduck24]

Your videos are everything. Thank you for your hard work!

[@excelisfun]

Glad it is "everything" for you, aduck24!!! Thanks for the support on each video with your comments, Thumbs Ups and Sub : )

[@ExceliAdam]

Load data from folder is amazing Power Query tool and extra files you gave just magnificent 🏆👍😊👍🏆

[@excelisfun]

Yes, Power Query is the best thing Microsoft has invented since the PivotTable!!! Glad the extra files are fun too. Thanks for the support, Excel.i Adam : )

[@lalitsharma9417]

Thanks. Its great learning source with homework to practice or revise the topics. 👍

[@niavras]

Great lesson. Thank you Mike!

[@DougHExcel]

Another great video! I'm glad on the Pivot Table Tabular format selection default capability in O365!

[@excelisfun]

Yes, Office 365 is the only Excel to have : ) Thanks for your support, Teammate!

[@chrism9037]

Mike, another incredible video! Happy Independence Day!

[@excelisfun]

You are welcome, Chris!!!! Thanks for watching on July 4th : )

[@MrSemyonoff]

Mike, on a 28:05 moment you could also click Refresh All twice and that would work too. Thanks for the great video!

[@excelisfun]

Cool, thanks for the tip, Alexandr!!! Thanks for watching and for your support : )

[@drsteele4749]

Powerful stuff. Super video Mike!!

[@excelisfun]

Thank you, DRSteele : ) Glad you like it !!!

[@ExcelCareerHub_1]

wonderful videos..best channel for Excel undoubtedly...Great work Mike

[@excelisfun]

Glad all my videos help, Simple Solutions!!!!

[@shubhampawar8506]

❤️😍love your teaching style

[@excelisfun]

: ) : )

[@wonjinpark7502]

Just Great! Cannot wait for next lecture!!

[@excelisfun]

Just Thanks, Wonjin!!!! Thank you for your support : )

[@timwhite8952]

Power Query fireworks. Great video. Thanks for posting.

[@excelisfun]

You are welcome, Tim!!! Thanks for your support!!

[@AweshBhornya-ExcelforNewbies]

One more video completed in this series. I am sure no book is going to be so comprehensive. Mike Girvin you are the master. This video was even better. I am sure this has solved the problems of many people who have problems with so many workbooks where they need to combine the data and perform analysis on it. Your video series keeps getting better and better every time i move ahead. Looking forward to your training videos. Thanks a ton. Just one last thing, how is geographical data such as country name, state name and city name or zip code managed in Power BI, I have used Tableau and Tableau has a dedicated Geographical data type. Is there anything similar to that in Power BI.

[@elhamiliat8389]

Thank you for the great video, Just wondering if we could get power query to go to different folders to get data and combine them into a single file.

[@krn14242]

Thanks Mike. Adapting your O365 to my Excel 2013 is fun.

[@excelisfun]

Yes, there is quite a difference in the versions. You, my friend, should just get O365 ; )

[@HopeCastle2000]

You are the BEST TEACHER EVER!

[@excelisfun]

Thanks you for the kind words, Hope! I am glad that you like the free resources : ) Thanks for the support on each video with your comments, Thumbs ups and a Sub : )

[@kss2066]

Thank you for this class!

[@armondnazarian4455]

Beautifully explained!! :) Thanks

[@Ethachum-Pannanum]

Perfect video that I was looking, thanks🙏

[@rrrprogram8667]

ALWAYSSSSS Greattttt videossss..... never tired of watching ur videos..... Also, lately I have been doing lots of Rstudio..... for machine learning and other cool stuff...

[@excelisfun]

Glad the videos help, RRR!!!! Thanks for your support... I have never tried Rstudio... I guess it is because I spend all my time producing videos...

[@rrrprogram8667]

Ya.... I have come long way majorly contributed by YOUR videos.... your works is of highest level....

[@cakirankale]

Loved this one too. Thanks Mike.

[@excelisfun]

Love is good : )

[@amadorcamacho]

Sir, my respect. Thanks for this data.

[@AtulSharma-gf5vs]

Nice Video. *Excel.Workbook* function - superhero in this video. thanks Mike !!!

[@excelisfun]

Yes, indeed!!!!

[@TouchdownHouse]

Thanks very much. Another brilliant video.

[@kliskiewitz1]

some of the excel files that I put into my source folder are not recognized when i refresh the consolidating file. Any ideas why it does not "see" all the files?

[@filipsvakjaroski20]

Thank you Mike, great video :)

[@ahmedal-dossary4386]

Wonderful Mike, thanks for the video.

[@excelisfun]

You are welcome for the wonderful, Ahmed : )

[@user-ms9hs2qg8o]

Amazing Video thank you. I was wondering how import from a folder and take hidden sheets from the excel files in power query?

[@madhubhimanapelli6271]

Wow... That was amazing 😍, need to learn more n more Thanks a lot sir.....

[@excelisfun]

You are welcome!

[@AmrMatar]

Thank you so much; amazing lessons

[@celia5132]

I've watched this video 5 times and it changed my way of reporting compeletely. Thank you so much Mike for your time and help! It requires so much effort to create great content like this. I have a question related to this method and I haven't found an answer so far. I'll be highly appreciate if you could kindly direct me to the right direction or adive some key words for more research. My question is that if one of my datasource files has more collumns than the others, and I want the data to be shown for my pivot table calculation. Is it possible? I've tried to update the file, but didn't success.

[@nattawut_chatwiriyacharoen]

Hi Mike, Thanks a lot for the video. Actually I have seen this example in one of your Power Query series, but this video is go into another level. I like the way you explain details of each step, and building a picture to illustrate a concept. It made me clearly understand. Furthermore, you also gave a pdf file which it contained every techniques of the video. You're amazing! P.S. Another option to refresh both Table (linked to Power Query) and Pivot Table (linked to the Table) at the same time is using Ctrl+Alt+F5. However, it will be good if the Refresh All button can do ^___^

[@excelisfun]

I am glad that the pictures help to illustrate the concepts!!! If you like the pictures to help tell the story, there is much more to come in this class, especially with DAX Formulas. Thanks for the refresh tip, but I thought I did show that in this video... Maybe it was video 03. Thank you for your support, Nattawut, with your comment, Thumbs up, Subscription, and pleas etll all your friends about the fun pictures and Excel cool tricks : )

[@nattawut_chatwiriyacharoen]

I shared your video last week and the feedback was good. Lots of Likes & Shares. You can see it from this URL facebook.com/reportingengineer/posts/2159495010960272 Thanks again for the video. I'm a big fan of you. If you have a chance to visit Thailand, please give me buy you lunch(s) ^___^

[@excelisfun]

Thanks for your support! There are many more videos in this series, and over the next nine months I will post about 1 - 2 videos a week : )

[@nattawut_chatwiriyacharoen]

I'm looking forward to seeing videos of this series. It's amazing and so fun. ^__^ (Actually, all of your videos are fun)

[@bebugerbebuger8617]

Great video Mike. I have a question, is there a way to separate with a space de names of the cities for example convert "SanFrancisco" into "San Francisco"?

[@tanweerabbasabbas649]

simply lovely!!

[@sazzarello]

I really got a lot out of that video. playlist has 161....great!...and thanks!

[@excelisfun]

Glad to hear that, Stephen! Thanks for the support with your comments and thumbs ups : )

[@shaunmcelrath6401]

This is amazing! Suggestions for when the data is not the same? I'm trying to pull in data from different sources (application cost, allocation, department) with the goal of providing reports that calculate total cost of ownership for all employees.

[@nimanitto]

wonderful as same as always. great job man!

[@excelisfun]

Glad it is helpful, Nima : )

[@nsanch0181]

Excellent Mike! Thank you.

[@excelisfun]

You are welcome, Nick! Thanks for the support : )

[@MohamedAlyCLAY]

i always use VBA in this situation .. but most power point of this method is if you add file to this folder .. just refresh connection :) thanks for this very cool series.

[@excelisfun]

Yes, Microsoft really hit a home-run with Power Query and the new Power Tools! No need for VBA here. You are welcome for the very cool series, Mohamed!!!!

[@Neolaxmi]

Thank you the the Amazing superlative mike !!

[@antrikshsharma6990]

Amaaaaazinggg videooooo oooon paaawer queeryyy!! 👍🏻 Liked the video first, commented next and now i will watch ha!

[@excelisfun]

Thank you very much for your support - it does help me to keep going with the long hours of creating fun content : )

[@antrikshsharma6990]

ExcelIsFun Hey Mike when do we see more ExceisFun vs Mr Excel videos? It has been a couple of months now. P.s - love that Spiderman kind of intro

[@excelisfun]

I am not sure when more Duels happen. Mr Excel is in the middle of a huge book project right now : ) Soon, I hope.

[@Meolimo]

I Realy love it ! Merci pour les explications tres claires

[@excelisfun]

Je vous en prie! Merci pour le support, Mario, avec ton commentaire, Thumbs Up et Subscription:) You are welcome! Thank you for the support, Mario, with your comment, Thumbs Up and Subscription : )

[@himanshudalai1028]

Thank you Mike for this great video.

[@excelisfun]

You are welcome, HIMANSHU! Thank you for helping with support of comments, Thumbs Up and Sub : )

[@munyaradzidamson2214]

Mike, you are the best.

[@PedroCabraldaCamara]

Awesome as usual. Thank you very much!

[@excelisfun]

You are welcome very much, Pedro!!!! Thank you for your support : ) : )