Download Excel START Files:
https://ift.tt/2L6uYCT
https://ift.tt/2L6jfYj
Download Excel FINISHED Files:
https://ift.tt/2La3uMM
https://ift.tt/2L9mZsh
Download pdf Notes about Power Query: https://ift.tt/2LbMtSy
Assigned Homework:
Download Excel File with Homework: https://ift.tt/2KZofOx
Example of Finished Homework: https://ift.tt/2Le3lYU
In this Video learn all about Excel.CurrentWorkbook Power Query Function to import all the Excel Tables in the Current Workbook, including all the potential pitfalls of using this function and how to get around these pitfalls.
Topics:
1. (00:15) Introduction to Video and to Excel.CurrentWorkbook Function, including correct definition of what Excel.CurrentWorkbook Function imports when this function is invoked.
2. (02:10) Example1: Append all Excel Tables in Current Workbook To Worksheet. We will see the Recursion Problem and solve it by filtering out the Query/Table Name.
3. (03:15) Create a Blank Query.
4. (04:10) Use Excel.CurrentWorkbook() Function as Source for Query.
5. (04:50) Use Replace feature to extract the SalesRep name from the Excel Table Name.
6. (05:38) Expand column with Excel Tables to Append all Tables into one Table.
7. (05:56) Add correct Data Types for each column
8. (06:22) Introduction to Recursion Problem, where Query will refer to itself and will double the loaded records every time a Refresh is done. And look at details of Loading Data to an Excel Worksheet after using Excel.CurrentWorkbook() Function.
9. (10:07) Solve the Recursion Problem by filtering out the Query/Table Name.
10. (11:03) Add new Excel Table to Workbook and refresh to see that new table is incorporated into Final Data Set.
11. (11:40) Look at M Code for Example #1
12. (12:20) Example2: Append all Excel Tables in Current Workbook To PivotTable Cache & make PivotTable Report. This solves the Recursion Problem because there is not a Query Load table in the Excel Worksheet as an Excel Table.
13. (12:41) Remove Excel Table that is result of Power Query Load to Worksheet by Clearing All. This process will change the Load location to Connection Only.
14. (13:38) Edit Query to Remove unneeded step and to Rename incorrectly named column.
15. (14:28) Look at M Code for Example #2
16. (15:05) Example3: Append all Excel Tables in Current Workbook that has Defined Names.
17. (15:12) Look at different objects in Excel workbook, including Excel Tables and Defined Names.
18. (17:07) Keyboard for Blank Query
19. (17:40) Use Excel.CurrentWorkbook() Function as Source for Query. Then see that this imports Excel Tables and Other Objects.
20. (17:51) Define Table Object: Set of Records for a Set of Columns/Fields.
21. (18:50) Take note that Defined Names are Imported as Tables with generic Columns Names.
22. (19:50) Learn about Table.ColumnNames Power Query Function.
23. (19:59) Filter out Filtered Database Error.
24. (20:29) Create Custom Column and use Table.ColumnNames Power Query Function to Extract Column Names from each Table in each Row.
25. (20:55) Learn about Lookup Operator or Field Access Operator to access the content for each row in a specified column.
26. (21:08) Define List Object: Ordered Sequence of Values.
27. (21:35) Learn about the Positional Index Operator that allows us to access an item in a list. Curly Brackets are the Positional Index Operator; { and } .
28. (22:27) Learn that Power Query uses Base Zero for finding Relative Positions in a List. Zero represents the first position.
29. (23:06) Filter out rows that contain “Column1”.
30. (23:18) Remove Custom Column.
31. (23:23) Rename Column
32. (23:27) Use Replace feature to extract the SalesRep name from the Excel Table Name
33. (23:41) Filter Out Query Name / Table Load Name when loading to an Excel Worksheet.
34. (24:07) Expand Columns and Change Data Types
35. (24:24) Closes and Load To Worksheet.
36. (24:46) Add new Excel Table and Refresh.
37. (25:07) Look at M Code for Example #3
38. (26:25) Talk about the non-standard Data Setup we had to deal with.
39. (26:51) Summary
Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.