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