1 ) Introduction to Video and to Excel.CurrentWorkbook Function, including correct definition of what Excel.CurrentWorkbook Function imports when this function is invoked. 1:54
2 ) 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. 1:04
3 ) Create a Blank Query. 0:54
4 ) Use Excel.CurrentWorkbook() Function as Source for Query. 0:39
5 ) Use Replace feature to extract the SalesRep name from the Excel Table Name. 0:47
6 ) Expand column with Excel Tables to Append all Tables into one Table. 0:17
7 ) Add correct Data Types for each column 0:25
8 ) 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 … 3:44
9 ) Solve the Recursion Problem by filtering out the Query/Table Name. 0:55
10 ) Add new Excel Table to Workbook and refresh to see that new table is incorporated into Final Data Set. 0:36
11 ) Look at M Code for Example #1 0:39
12 ) 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 … 0:20
13 ) 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. 0:56
14 ) Edit Query to Remove unneeded step and to Rename incorrectly named column. 0:49
15 ) Look at M Code for Example #2 0:36
16 ) Example3: Append all Excel Tables in Current Workbook that has Defined Names. 0:06
17 ) Look at different objects in Excel workbook, including Excel Tables and Defined Names. 1:54
18 ) Keyboard for Blank Query 0:32
19 ) Use Excel.CurrentWorkbook() Function as Source for Query. Then see that this imports Excel Tables and Other Objects. 0:10
20 ) Define Table Object: Set of Records for a Set of Columns/Fields. 0:58
21 ) Take note that Defined Names are Imported as Tables with generic Columns Names. 0:59
22 ) Learn about Table.ColumnNames Power Query Function. 0:08
23 ) Filter out Filtered Database Error. 0:29
24 ) Create Custom Column and use Table.ColumnNames Power Query Function to Extract Column Names from each Table in each Row. 0:25
25 ) Learn about Lookup Operator or Field Access Operator to access the content for each row in a specified column. 0:12
26 ) Define List Object: Ordered Sequence of Values. 0:26
27 ) Learn about the Positional Index Operator that allows us to access an item in a list. Curly Brackets are the Positional Index Operator; { and } . 0:51
28 ) Learn that Power Query uses Base Zero for finding Relative Positions in a List. Zero represents the first position. 0:38
29 ) Filter out rows that contain “Column1”. 0:11
30 ) Remove Custom Column. 0:04
31 ) Rename Column 0:03
32 ) Use Replace feature to extract the SalesRep name from the Excel Table Name 0:13
33 ) Filter Out Query Name / Table Load Name when loading to an Excel Worksheet. 0:25
34 ) Expand Columns and Change Data Types 0:16
35 ) Closes and Load To Worksheet. 0:21
36 ) Add new Excel Table and Refresh. 0:20
37 ) Look at M Code for Example #3 1:17
38 ) Talk about the non-standard Data Setup we had to deal with. 0:25
39 ) Summary