Welcome to another awesome<\/strong> power query tutorial<\/strong>!<\/p>\n Download Excel START Files: Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.<\/p>\n
\nhttps:\/\/ift.tt\/2L6uYCT
\nhttps:\/\/ift.tt\/2L6jfYj
\nDownload Excel FINISHED Files:
\nhttps:\/\/ift.tt\/2La3uMM
\nhttps:\/\/ift.tt\/2L9mZsh
\nDownload pdf Notes about Power Query: https:\/\/ift.tt\/2LbMtSy
\nAssigned Homework:
\nDownload Excel File with Homework: https:\/\/ift.tt\/2KZofOx
\nExample of Finished Homework: https:\/\/ift.tt\/2Le3lYU
\nIn this Video learn all about Excel.CurrentWorkbook Power Query<\/strong> 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.
\nTopics:
\n1. (00:15) Introduction to Video and to Excel.CurrentWorkbook Function, including correct definition of what Excel.CurrentWorkbook Function imports when this function is invoked.
\n2. (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.
\n3. (03:15) Create a Blank Query.
\n4. (04:10) Use Excel.CurrentWorkbook() Function as Source for Query.
\n5. (04:50) Use Replace feature to extract the SalesRep name from the Excel Table Name.
\n6. (05:38) Expand column with Excel Tables to Append all Tables into one Table.
\n7. (05:56) Add correct Data Types for each column
\n8. (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.
\n9. (10:07) Solve the Recursion Problem by filtering out the Query\/Table Name.
\n10. (11:03) Add new Excel Table to Workbook and refresh to see that new table is incorporated into Final Data Set.
\n11. (11:40) Look at M Code for Example #1
\n12. (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.
\n13. (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.
\n14. (13:38) Edit Query to Remove unneeded step and to Rename incorrectly named column.
\n15. (14:28) Look at M Code for Example #2
\n16. (15:05) Example3: Append all Excel Tables in Current Workbook that has Defined Names.
\n17. (15:12) Look at different objects in Excel workbook, including Excel Tables and Defined Names.
\n18. (17:07) Keyboard for Blank Query
\n19. (17:40) Use Excel.CurrentWorkbook() Function as Source for Query. Then see that this imports Excel Tables and Other Objects.
\n20. (17:51) Define Table Object: Set of Records for a Set of Columns\/Fields.
\n21. (18:50) Take note that Defined Names are Imported as Tables with generic Columns Names.
\n22. (19:50) Learn about Table.ColumnNames Power Query Function.
\n23. (19:59) Filter out Filtered Database Error.
\n24. (20:29) Create Custom Column and use Table.ColumnNames Power Query Function to Extract Column Names from each Table in each Row.
\n25. (20:55) Learn about Lookup Operator or Field Access Operator to access the content for each row in a specified column.
\n26. (21:08) Define List Object: Ordered Sequence of Values.
\n27. (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 } .
\n28. (22:27) Learn that Power Query uses Base Zero for finding Relative Positions in a List. Zero represents the first position.
\n29. (23:06) Filter out rows that contain \u201cColumn1\u201d.
\n30. (23:18) Remove Custom Column.
\n31. (23:23) Rename Column
\n32. (23:27) Use Replace feature to extract the SalesRep name from the Excel Table Name
\n33. (23:41) Filter Out Query Name \/ Table Load Name when loading to an Excel Worksheet.
\n34. (24:07) Expand Columns and Change Data Types
\n35. (24:24) Closes and Load To Worksheet.
\n36. (24:46) Add new Excel Table and Refresh.
\n37. (25:07) Look at M Code for Example #3
\n38. (26:25) Talk about the non-standard Data Setup we had to deal with.
\n39. (26:51) Summary<\/p>\n