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

Подписаться 1 млн

Просмотров 184 тыс.

50% 1

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.

Опубликовано:

2 июл 2018

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 423

@excelisfun 6 лет назад

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

@brianxyz 6 лет назад

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.

@GeertDelmulle 6 лет назад

Majestic Mike, simply majestic, Mike! You are the Very Best -- bar NONE! :-)

@patrickbanzon7145

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

@williamarthur4801 2 года назад

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

@panh4190 Год назад

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

@shoeshines2121 4 года назад

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

@mazarata73 5 лет назад

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.

@Whyvardhann 6 лет назад

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

@tableaupro3233 5 лет назад

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

@petermyran4986 6 лет назад

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.

@fredzfranz09 5 лет назад

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.

@MrNinjagofan 3 года назад

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

@mihailela2267 5 лет назад

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.