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

Download START Files:

Assigned Homework:
Download Word Document and read:
Then download the rest of the files and complete the homework:
Examples of Finished Homework:

Buy excelisfun products:

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.

[patrickbanzon]

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

[panh]

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

[__HumanBeing]

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

[williamarthur]

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

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

[fredzfranz]

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!

[rrrprogram]

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

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

[afzalzamiransari]

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

[timmytesla]

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

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

[Whyvardhann]

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

[denisdwyer]

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

[hollydunne]

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.

[ismailismaili]

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

[BillSzysz]

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

[sequa]

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

[tableaupro]

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.

[மின்விரிதாள்_விரிப்போம்_வாங்க]

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!