Skip to main content
AmigoFish

Power Query #6: 12 Tables Into One Table With Just A Click: Combine Files Button

From excelisfun · · 12 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/PowerQuery06Files.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/ This video teaches you how to use the From Folder feature and Combine Files button to append files from a folder, whether Csv files, Excel files or other file types. 12 tables into one table with just a click! Topics: 1. (00:00) Intro Song 2. (00:11) Introduction 3. (00:42) Download files so you can practice 4. (01:06) Folders of files, On Premises Folder Path, and how the structure of the data in each file MUST be the same 5. (01:34) Folders with CSV Files 6. (01:43) Folders with Excel Files 7. (01:47) VERY IMPORTANT NOTES about structure of Excel files 8. (03:33) Import folder of CSV files using the From File / From Folder feature in Excel 9. (03:48) Import folder of CSV files using the Folder feature in Power BI Desktop 10. (04:32) Binary M Code Values 11. (04:51) Filter out non-CSV files 12. (05:20) Table.SelectRows function 13. (05:38) Remove Other Columns (Keep Content Column Only) 14. (05:53) Table.SelectColumns function 15. (06:08) Combine Files button to create helper queries, query steps, and append all the tables. 16. (06:23) Make sure delimiter is a comma 17. (06:44) What the Helper Queries, Custom Function, and New Applied Query Steps do and what they mean 18. (08:51) Load the Appended Table to the Worksheet 19. (09:14) Add new CSV files to the folder, refresh query and the Appened table updates with new data 20. (10:06) From Folder feature and Combine Files button to append data from 12 Excel files 21. (10:35) Make sure to select an Excel Worksheet object that represents the structure in all the other Excel files to this automatic process can build the correct M Code for the Append Query 22. (11:09) Look at Automatic M Code that assures that only Worksheets named “Sheet1” are imported when the custom function is used. 23. (11:29) Custom Function is invoked in each row of the table with the Binary Column filled with Excel files 24. (11:51) Close and load new append table to the worksheet. 25. (12:00) Summary 26. (12:24) Closing

This video has subtitles or audio translations in these 152 languages: Abkhazian, Afar, Akan, Albanian, Amharic, Arabic, Armenian, Assamese, Aymara, Azerbaijani, Bambara, Bangla, Bangla (India), Bashkir, Basque, Belarusian, Bhojpuri, Bosnian, Breton, Bulgarian, Burmese, Cantonese, Cantonese (Hong Kong), Catalan, Chinese, Chinese (China), Chinese (Hong Kong), Chinese (Singapore), Chinese (Taiwan), Corsican, Croatian, Czech, Danish, Dogri, Dutch, Dutch (Belgium), Dutch (Netherlands), Dzongkha, Esperanto, Estonian, Ewe, Faroese, Fijian, Filipino, Finnish, French, French (Belgium), French (Canada), French (France), French (Switzerland), Fula, Galician, Ganda, Georgian, German, German (Austria), German (Germany), German (Switzerland), Greek, Guarani, Gujarati, Haitian Creole, Hausa, Hawaiian, Hebrew, Hindi, Hungarian, Icelandic, Indonesian, Inuktitut, Irish, Italian, Japanese, Javanese, Khmer, Korean, Kurdish, Lao, Latin, Latvian, Lingala, Lithuanian, Luxembourgish, Macedonian, Maithili, Malagasy, Malay, Malay (Singapore), Malayalam, Maltese, Marathi, Mongolian, Nepali, Norwegian, Papiamento, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Portuguese (Portugal), Punjabi, Romanian, Rundi, Russian, Samoan, Sango, Sanskrit, Santali, Scottish Gaelic, Serbian, Serbian (Cyrillic), Serbian (Latin), Shona, Sicilian, Sindhi, Sinhala, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Sundanese, Swahili, Swati, Swedish, Tajik, Tamil, Tatar, Telugu, Thai, Tibetan, Tigrinya, Tongan, Tsonga, Tswana, Turkish, Turkmen, Ukrainian, Urdu, Uyghur, Uzbek, Venda,…

Back to excelisfun · Report a problem