Completed
) Use Excel.CurrentWorkbook() Function as Source for Query. Then see that this imports Excel Tables and Other Objects.
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
