Skip to main content
AmigoFish

Build Power Query Custom Function: Convert Multiple Csv Cross Tab Tables into Single Table EMT 1917.

From excelisfun · · 18 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: 1) Excel File: https://excelisfun.net/files/EMT1917-1919.xlsx 2) Zipped Data Folder: https://excelisfun.net/files/SeattleAirportData.zip Buy excelisfun’s Power Query M Code book: https://www.mrexcel.com/products/the-transformative-magic-of-power-query-m-code-in-excel-and-power-bi/ Buy excelisfun’s other Excel Books: https://www.amazon.com/stores/Mike-excelisfun-Girvin/author/B009Q5T4P2?ref=ap_rdr&ccs_id=6f8ce877-462f-447e-b825-7ede1f72a9d3 Free Excel Class from excelisfun: https://www.youtube.com/playlist?list=PLrRPvpgDmw0k7ocn_EnBaSJ6RwLDOZdfo Learn how to import multiple Csv files that each have a cross tabulated data structure and build a Custom Power Query Function to convert each table to a proper table and then append them all into a single table. Learn a lot about M Code to build Custom Functions. Topics: 1. (00:00) Introduction. 2. (00:28) Preview of videos 1917, 1918, and 1919! 3. (01:03) excelisfun’s Power Query M Code Book. 4. (01:26) Goal of Video. 5. (01:38) Preview files used in video. Structure of data files. 6. (02:26) The ways to import, transform and append cross tabulated cdv files. The three methods will be taught in videos 1917, 1918, 1919. 7. (03:00) Open Queries and Connections. 8. (03:15) From Folder feature to import multiple Csv files from Folder. 9. (04:11) On-Premise Folder Path. 10. (04:55) Preview of “Hack” method, or “Manual Method” of building code for the Custom Function in the query that uses the Custom Function. 11. (05:09) Extract single Csv file and create code for Custom Function. 12. (06:10) Keyboard shortcut to name columns in a Power Query Editor Table from dmclean3324 at YouTube. 13. (07:10) Notice that in last video Power Query interpreted empty cells in an Excel Table as null values, but Power Query interpreted empty cells in a Csv file as blank (zero length text string) values. 14. (08:01) Advanced Editor. 15. (08:17) What is a let expression? 16. (09:45) Create a note in the Advanced Editor. 17. (10:34) Cut M Code from first Query and paste it into a new query. 18. (11:42) Create a Custom Function. What is a Custom Function? 19. (14:13) Invoke Custom Function in table with Csv files. 20. (15:10) Append or Combine tables into single table. 21. (16:34) Summary of import, build Custom Function, Invoke Function and Append. 22. (17:00) Load new table to Worksheet. 23. (17:11) Add new Csv files to folder and refresh: AND everything updates with new data!! 24. (17:57) Summary 25. (18:05) Closing

#excel #microsoft #excelisfun #excelcourse #mikegirvin #excel365 #powerquery #customfunction #powerqueryfunction #powerquerycustomfunction #importmultiplefiles #letexpression #letstatement #mcode #powerquerymcode #importdata #exceldataanalysis #powerbidataanalysis

This video has subtitles or audio translations in these languages: Abkhazian, Afar, Afrikaans, Akan, Albanian, Amharic, Arabic (experimental), Armenian, Assamese, Aymara, Azerbaijani, Bambara, Bangla (experimental), Bangla (India), Bashkir, Basque, Belarusian, Bhojpuri, Bosnian, Breton, Bulgarian, Burmese, Cantonese, Cantonese (Hong Kong), Catalan, Chinese, Chinese (China), Chinese (Hong Kong), Chinese (Simplified), Chinese (Singapore), Chinese (Taiwan), Chinese (Traditional), Corsican, Croatian, Czech, Danish, Dogri, Dutch, Dutch (Belgium), Dutch (Netherlands), Dzongkha, English, English (Australia), English (Canada), English (India), English (Ireland), English (United Kingdom), 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 (experimental), Hindi, Hungarian, Icelandic, Igbo, Indonesian, Inuktitut, Irish, Italian, Japanese, Javanese, Kalaallisut, Kannada, Kazakh, Khmer, Kinyarwanda, Konkani, Korean (experimental), Kurdish, Kyrgyz, Lao, Latin, Latvian, Lingala,…

Back to excelisfun · Report a problem