Skip to main content
AmigoFish

Power Query #11: Excel.CurrentWorkbook Power Query M Code Function

From excelisfun · · 13 min

The player loads from YouTube only when you click. It uses YouTube's privacy-enhanced mode.

If the video does not play, the owner may have turned off embedding. Watch it on YouTube instead.

Watch on YouTube

Not rated yet

Description

Download Zipped folder with files: https://excelisfun.net/files/PowerQuery11Files.zip Free Power Query YouTube class from Mike excelisfun Girvin: https://www.youtube.com/playlist?list=PLrRPvpgDmw0lHaJfr4mjRcOMEYqKKvKBj Buy Mike Girvins Power Query M Code book: https://www.amazon.com/Transformative-Magic-Power-Query-Excel/dp/1615470832/ In this video learn all about how the Excel.CurrentWorkbook function imports the contents of the current workbook into the Power Query Editor. Learn how to append all Excel Tables in the current workbook into a single table. Learn about the hidden pitfalls of the Excel.CurrentWorkbook function. Convert sub-tables into consolidated final table. Topics: 1. (00:00) Introduction 2. (00:44) Download File 3. (00:54) What Excel.CurrentWorkbook function does 4. (02:30) Source Data: Excel Tables with Consistent Naming Convention 5. (02:52) Examples of all objects that Excel.CurrentWorkbook can import into Power Query Editor from the Current Workbook, including Excel Tables, Defined Names, Advanced Filter Names, Filter Names, Set Print Area Names, and Dynamic Spilled Array Names 6. (03:53) Recursion Problem with Excel.CurrentWorkbook function. How to stop Recursion. 7. (04:35) 8. (04:44) Four Methods to create Blank Query with the Excel.CurrentWorkbook function: 9. (04:49) 1) Data, Get & Transform, Get Data Dropdown, From Other Sources, Blank Query 10. (05:36) 2) Add Blank Query Button to Quick Access Toolbar 11. (06:08) 3) Keyboard to access Blank Query button in the Quick Access Toolbar 12. (06:24) 4) From Table Range “Hack” Option 13. (07:14) Excel.CurrentWorkbook 14. (07:34) Filter to Only Import the desired Excel Tables 15. (07:54) Replace SalesTable with nothing 16. (08:08) Rename Column 17. (08:13) Expand Content 18. (08:28) Add Data Types 19. (08:43) Load query 20. (09:03) Copy Worksheet Quick Method 21. (09:55) Refresh query when new Excel Table with consistent name is added to workbook 22. (10:11) Importing Dynamic Spilled Array Formula and the automatic Defined Name 23. (11:05) Recursion problem and how to fix it, in second Excel file 24. (11:17) LAMBDA Defined Name not imported when Excel.CurrentWorkbook is used 25. (13:03) Summary 26. (13:14) Mike Girvin’s Power Query Book 27. (13:23) Closing

This video has subtitles or audio translations in these 93 languages: Afrikaans, Arabic, Armenian, Bambara, Bangla, Basque, Belarusian, Bhojpuri, Bulgarian, Burmese, Cantonese, Chinese, Croatian, Czech, Danish, Dutch, Dutch (Netherlands), Estonian, Faroese, Fijian, Filipino, Finnish, French, French (France), Georgian, German, German (Germany), Greek, Hawaiian, Hebrew, Hindi, Hungarian, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean, Kurdish, Lao, Latvian, Lithuanian, Luxembourgish, Macedonian, Maithili, Malagasy, Malay, Malayalam, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Punjabi, Romanian, Russian, Samoan, Sanskrit, Scottish Gaelic, Serbian, Sicilian, Sinhala, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Swahili, Swedish, Tajik, Tamil, Tatar, Telugu, Thai, Tibetan, Tongan, Tswana, Turkish, Turkmen, Ukrainian, Urdu, Vietnamese, Welsh, Zulu. Click gear icon below video to set subtitles, audio and language.

#excel #excel365 #excelisfun #mikegirvin #freeexcellessons #excelformulas #excelfunctions #excelisfun #MikeGirvin #PowerQuery #Excel #PowerBI #PowerBIDesktop #Excel365 #LearnPowerQuery #freeclass #DataAnalysis #BusinessIntelligence #Microsoft

Back to excelisfun · Report a problem