Skip to main content
AmigoFish

Power Query Nested Functions in Custom Column: Convert Multiple Csv Files into One Table. EMT 1918

From excelisfun · · 15 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 Column formula with Nested Functions to convert each table to a proper table and then append them all into a single table. Learn a lot about M Code in this video. Topics: 1. (00:00) Introduction. 2. (00:23) Preview of videos 1917, 1918, and 1919! 3. (00:47) excelisfun’s Power Query M Code Book. 4. (01:15) The problem with storing data in a cross tabulated table. 5. (01:59) Goal of video and look at files we are working with. 6. (02:34) From Folder feature to import multiple csv files. 7. (03:08) Add Custom Column and build formula step by step. 8. (03:52) Csv.Document Power Query M Code function. 9. (05:08) Table.Transpose Power Query M Code function. 10. (05:33) Tip about IntelliSense and accepting function names from the dropdown list: type all lowercase with no spaces and no periods. 11. (06:03) Table.PromoteHeaders Power Query M Code function. 12. (06:30) Table.RenameColumns Power Query M Code function. 13. (07:59) Table.UnpivotOtherColumns Power Query M Code function. 14. (09:14) Table.SelectRows Power Query M Code function. 15. (10:31) Table.TransformColumnTypes Power Query M Code function. 16. (10:35) Tip to help remember M Code: add step, copy M Code, delete step, use M Code in a different step. 17. (11:43) Finish step that uses the Table.TransformColumnTypes Power Query M Code function. 18. (12:46) Lookup a column from a table and return as a list. Syntax = Table[ColumnName] = content of column as a list. 19. (13:17) Table.Combine Power Query M Code function. 20. (13:32) Summary 21. (13:45) Load finished table. 22. (13:57) Test formula by adding new files to the folder. 23. (14:57) Fun with Mega-Nested M Code Formulas 24. (15:04) 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: Afrikaans, Amharic, Arabic (experimental), Bangla (experimental), Bosnian, Bulgarian, Cantonese, Chinese, Croatian, Czech, Danish, Dutch, Dutch (Netherlands), Estonian, Filipino, Finnish, French, French (France), Georgian, German, German (Germany), Greek, Hebrew (experimental), Hindi, Icelandic, Indonesian, Irish, Italian, Japanese, Khmer, Korean (experimental), Latvian, Lithuanian, Luxembourgish, Macedonian, Malagasy, Malay, Malayalam, Mongolian, Nepali, Norwegian, Pashto, Persian, Persian (Afghanistan), Persian (Iran), Polish, Portuguese, Portuguese (Brazil), Punjabi (experimental), Russian (experimental), Sanskrit, Scottish Gaelic, Serbian, Sicilian, Slovak, Slovenian, Somali, Spanish, Spanish (Latin America), Spanish (Mexico), Spanish (Spain), Spanish (United States), Swahili, Swedish, Tajik, Tamil (experimental), Tatar, Telugu (experimental), Thai, Tibetan, Turkish, Turkmen, Ukrainian, Urdu, Uzbek, Vietnamese, Welsh, Zulu. Click gear icon below video to set subtitles, audio and language.

Back to excelisfun · Report a problem