MSPTDA 05: Power Query: Excel.CurrentWorkbook Function to Append All Excel Tables in Excel Workbook

Download Excel START Files:
Download Excel FINISHED Files:
Assigned Homework:

Buy excelisfun products:

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

Comprehensive Microsoft Power Tools for Data Analysis Class, BI 348, taught by Mike Girvin, Excel MVP and Highline College Professor.

[otaorojuolalekan]

Wow! How can I like youtube videos multiple times???
You're a bomb MIKE.
Words can't quantify how much I learned from you.
God bless you real good.

[rodrigocustodio]

Mike, the minimum we can do is support you somehow, being with comments, thumbs up, or buying a shirt or something. In my opinion, you are the best at teaching excel plus all the materials you provide are really good, all your support is outstanding. I am learning so much and I am thankful for your channel.

[cakirankale]

If I don't get the concept once, second time slow and steady is enough. So amazingly explained. And when you get the usefulness of something, you feel why all are not learning it. Grateful Mike.

[williamarthur]

It's so great that the files are provided so you can follow along, AND it worked.

[garrylingle]

I have watched a lots of Excel videos from many different sources. I've learned a LOT about Power Query, Power Pivot, and general Excel. I still come back to your channel because I like the way you do and present things, and I still learn new things. THANK YOU doesn't really convey my appreciation for all that you've done and made available.

[frisovandermeulen]

I don’t know how much time and frustrations you saved me with your explanation of the rucursive problem; I can imagine clearly the proces I would have gone through if I did not see this lesson. Thanks a lot!

[timmytesla]

My third PowerQuery video. Now, I go do the homework. Thank you Mike!

[shoeshines]

Thank you ExcelIsFun! For people watching the video, please hit "like" and leave a comment. This is easily the best channel for Excel Power Tools on YouTube!

[chandrathe]

I still come back to your MSPTDA channel to repeat the epic stuf of this serie, extrem usefull! Thanks for sharing, love the way you show and teach. Thumbs up,

[alucc]

Thank you Mr. Mike. I'm here after completing the EDAB series, I'm learning a lot!

[andrewhirst]

I always come back to your videos, working through the MSPTDA series to the end. I find your training invaluable as where I work as A Data Analyst in the UK no training is provided!!.
All the best.

[ismailismaili]

I really love to watch your series continuously because I feel so happy with learning so many info every week. thanks as always Mr. Mike

[chavelooo]

For me this guy is like Messi or Cr Ronaldo. You are the best. Explaining, teaching and so on. I am so interested to continue learning.

[armondnazarian]

The amount of knowledge you teach in your videos in a short period of time is amazing! :) Thanks

[fmohiy]

One of best teachers ever. Never a dropped step or ever wondering how you got from one step to another. You deserve more recognition. God bless you.

[rws]

This channel is too crazy man.. I have tried many different sources; in written or in video forms, but this one is simply the bestest best. Thanks to the author for bringing the knowledge to us.

[gregfollett]

I love, love loved this video. I have learned so much from watching your FREE online courses, I am using it on the job and impressing my boss. Thank you Mike, you're amazing!

[rodrigocustodio]

Thank you, Mike, as all your Excel videos your MSPTDA series is awesome, the truth is you are the best!

[arvindmarathe]

I not heard about power query earlier but with your videos I understand the power quiry and how it makes work simple. Thanks you so much for this

[anisdadani]

Masha Allah, you are doing great work. May Allah (SWT) less you with best.