Overview

Save Big on Coursera Plus. 7,000+ courses at $160 off. Limited Time Only!

Grab it

Learn how to use the Excel.CurrentWorkbook function in Power Query to append all Excel tables in a workbook. Master techniques for importing and manipulating data, including solving the recursion problem, extracting information from table names, and handling defined names. Explore practical examples of appending tables to worksheets and PivotTable caches, and discover how to work with various Excel objects. Gain insights into Power Query functions like Table.ColumnNames, and understand key concepts such as table objects, list objects, and positional index operators. Apply these skills to efficiently combine and analyze data from multiple tables within a single Excel workbook.

Syllabus

) Introduction to Video and to Excel.CurrentWorkbook Function, including correct definition of what Excel.CurrentWorkbook Function imports when this function is invoked..
) 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..
) Create a Blank Query..
) Use Excel.CurrentWorkbook() Function as Source for Query..
) Use Replace feature to extract the SalesRep name from the Excel Table Name..
) Expand column with Excel Tables to Append all Tables into one Table..
) Add correct Data Types for each column.
) 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..
) Solve the Recursion Problem by filtering out the Query/Table Name..
) Add new Excel Table to Workbook and refresh to see that new table is incorporated into Final Data Set..
) Look at M Code for Example #1.
) 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..
) 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..
) Edit Query to Remove unneeded step and to Rename incorrectly named column..
) Look at M Code for Example #2.
) Example3: Append all Excel Tables in Current Workbook that has Defined Names..
) Look at different objects in Excel workbook, including Excel Tables and Defined Names..
) Keyboard for Blank Query.
) Use Excel.CurrentWorkbook() Function as Source for Query. Then see that this imports Excel Tables and Other Objects..
) Define Table Object: Set of Records for a Set of Columns/Fields..
) Take note that Defined Names are Imported as Tables with generic Columns Names..
) Learn about Table.ColumnNames Power Query Function..
) Filter out Filtered Database Error..
) Create Custom Column and use Table.ColumnNames Power Query Function to Extract Column Names from each Table in each Row..
) Learn about Lookup Operator or Field Access Operator to access the content for each row in a specified column..
) Define List Object: Ordered Sequence of Values..
) Learn about the Positional Index Operator that allows us to access an item in a list. Curly Brackets are the Positional Index Operator; { and } ..
) Learn that Power Query uses Base Zero for finding Relative Positions in a List. Zero represents the first position..
) Filter out rows that contain “Column1”..
) Remove Custom Column..
) Rename Column.
) Use Replace feature to extract the SalesRep name from the Excel Table Name.
) Filter Out Query Name / Table Load Name when loading to an Excel Worksheet..
) Expand Columns and Change Data Types.
) Closes and Load To Worksheet..
) Add new Excel Table and Refresh..
) Look at M Code for Example #3.
) Talk about the non-standard Data Setup we had to deal with..
) Summary.