Transform and Combine all worksheets in a workbook with Power Query (English VO)

For Cantonese VO, please visit: https://youtu.be/Uq7-ZlvVwyk In this video, I am going to show you a real-life work example I've got recently from a colleague. She asked me if there is a quick way to consolidate (append) 100+ worksheets in an Excel Workbook. I said: "Of course! Only if you know how. " ? Indeed, we need to cleanse (transform) the worksheets into desired layout first before we can do the consolidation. And we need to preserve context from the worksheets. I am sure it would take her hours, if not day, to complete the task. With Power Query, it can be done in minutes. No kidding! Four major tasks involved: 1) Get a sample table (worksheet) from the workbook 2) Transform the table into the desired layout 3) Turn the transformation query into a custom function 4) Invoke the custom function to all the table (worksheets) in the workbook You may download the sample files to follow along. Target workbook (to be transformed and combined): https://drive.google.com/open?id=19Wy6nGzUN0oXDRodHyO59JPY2J513NFq Finished workbook (with the query): https://drive.google.com/open?id=1oJ1RzjKrGtXnxuSS8xZnccacQ5zqZuWy I hope you like this video. If you do, please give a thumb up, subscribe and share it. ?

Видео Transform and Combine all worksheets in a workbook with Power Query (English VO) автора Эксель для Работы с Инвестициями