Excel Power Query Remove Blank Rows & Columns in All Excel Files During Import. EMT 1660.

This video teaches how to remove all blank rows and columns in all Excel files during import using Power Query Custom Functions. See Two Methods for build Custom Functions to complete this task. These functions Streamline and Make Efficient this usually arduous task.
Topics:
1. (00:01) Introduction and Overview of entire data import and transform process.
2. (00:33) Jim’s Question.
3. (00:48) Look at Files that must be imported.
4. (01:23) Power Query to avoid manual tasks.
5. (01:47) Import Initial Excel Files using From Folder.
6. (02:26) Prepare File Name Column so we can use City Name in Final Table.
7. (02:23) Excel.Workbook Function to extract Objects from Excel File.
8. (03:20) Second argument in Excel.Workbook Function to promote Headers (Field Names). For example #1, we do NOT Promote Headers.
9. (03:35) Look at Table that Excel.Workbook Function Delivers.
10. (04:02) Two-Way Lookup with Positional Access Operator and Field Access Operator to extract the Sheet Object.
11. (05:09) Build First Custom Power Query Function using User Interface.
12. (05:24) Extract Sample Table to build M Code for Custom Function.
13. (06:03) Build M Code for Custom Function using User Interface with two Remove Blank Roes and Two Transpose Operations.
14. (06:57) Copy Code from Advanced Editor.
15. (07:08) Build First Custom Function in Blank Query.
16. (07:28) Understanding Let Statements in Power Query M Code.
17. (08:12) Define Custom Function with Variable and Go To Operator.
18. (09:01) Invoke Custom Function For Custom Column. Use Custom Function on Excel Sheet Data to Remove Blank Rows and Columns.
19. (09:45) Append Process For First Example.
20. (10:320 Load first example to Worksheet.
21. (10:48) Build Second Custom Power Query Function typing more efficient M Code.
22. (11:13) Second argument in Excel.Workbook Function to promote Headers (Field Names). For example #2, we Promote Headers.
23. (11:45) Conceptual Approach for Second M Code Custom Function. Code from Bill Szysz.
24. (12:36) Create Blank Query for Second Custom Function.
25. (12:53) How do we Visualize M Code, given that there is no F9 key, like in an Excel Worksheet Formula? See the Power Query M Code Function; Table.ColumnNames.
26. (14:40) Connect Function Variables to Columns.
27. (15:00) Power Query M Code Functions List.Select and Text.StartsWith.
28. (16:40) Table.SelectColumns Power Query M Code Function.
29. (17:08) Table.SelectRows and Record.Field Power Query M Code Function. See not comparative operator and the literal null.
30. (18:36) Table.ColumnNames with Positional Index Operator for first row.
31. (19:36) Use Variable for First Column Name.
32. (19:53) Table.AddColumn Power Query M Code Function.
33. (20:36) Table.Combine Power Query M Code Function.
34. (21:21) Load Second Appended Tables.
35. (21:31) Add new data and refresh.
36. (21:41) Summary and End Video Links

[excelisfun]

Topics:
1. (00:01) Introduction and Overview of entire data import and transform process.
2. (00:33) Jim’s Question.
3. (00:48) Look at Files that must be imported.
4. (01:23) Power Query to avoid manual tasks.
5. (01:47) Import Initial Excel Files using From Folder.
6. (02:26) Prepare File Name Column so we can use City Name in Final Table.
7. (02:23) Excel.Workbook Function to extract Objects from Excel File.
8. (03:20) Second argument in Excel.Workbook Function to promote Headers (Field Names). For example #1, we do NOT Promote Headers.
9. (03:35) Look at Table that Excel.Workbook Function Delivers.
10. (04:02) Two-Way Lookup with Positional Access Operator and Field Access Operator to extract the Sheet Object.
11. (05:09) Build First Custom Power Query Function using User Interface.
12. (05:24) Extract Sample Table to build M Code for Custom Function.
13. (06:03) Build M Code for Custom Function using User Interface with two Remove Blank Roes and Two Transpose Operations.
14. (06:57) Copy Code from Advanced Editor.
15. (07:08) Build First Custom Function in Blank Query.
16. (07:28) Understanding Let Statements in Power Query M Code.
17. (08:12) Define Custom Function with Variable and Go To Operator.
18. (09:01) Invoke Custom Function For Custom Column. Use Custom Function on Excel Sheet Data to Remove Blank Rows and Columns.
19. (09:45) Append Process For First Example.
20. (10:320 Load first example to Worksheet.
21. (10:48) Build Second Custom Power Query Function typing more efficient M Code.
22. (11:13) Second argument in Excel.Workbook Function to promote Headers (Field Names). For example #2, we Promote Headers.
23. (11:45) Conceptual Approach for Second M Code Custom Function. Code from Bill Szysz.
24. (12:36) Create Blank Query for Second Custom Function.
25. (12:53) How do we Visualize M Code, given that there is no F9 key, like in an Excel Worksheet Formula? See the Power Query M Code Function; Table.ColumnNames.
26. (14:40) Connect Function Variables to Columns.
27. (15:00) Power Query M Code Functions List.Select and Text.StartsWith.
28. (16:40) Table.SelectColumns Power Query M Code Function.
29. (17:08) Table.SelectRows and Record.Field Power Query M Code Function. See not comparative operator and the literal null.
30. (18:36) Table.ColumnNames with Positional Index Operator for first row.
31. (19:36) Use Variable for First Column Name.
32. (19:53) Table.AddColumn Power Query M Code Function.
33. (20:36) Table.Combine Power Query M Code Function.
34. (21:21) Load Second Appended Tables.
35. (21:31) Add new data and refresh.
36. (21:41) Summary and End Video Links

[reng]

My Friend, let me tell you that you are a Master Mind in Excel and its Power Tools!!!

[dirceushimizuschaack]

It is indeed a Power Query master class! Tons of good practices in a short video!

[bamakaze]

Mike, this may be your single greatest power query tutorial! The custom table function and headers trick are life savers!

[emmanuelagbo]

Really column. This took my power query knowledge to another level.

[stephanweaver]

Awesome graphics, thanks for all the hard work. I love the fact that you go quickly through it; it allows the user to grasp "where you're going" to better understand "where I am now". It also puts a good burden on the student to focus and practice

[wmfexcel]

WOW WOW WOW!
I've never thought of writing M code from scratch. When I watched you doing so, you made it so easy... but I know deep in my heart, it's a lot of hard work behind it. 👍
Thanks for sharing your knowledge!

[henrytran]

Many thanks, master Mike. You are awesome.

[jerrydellasala]

One of the best, most practical on PQ!

[roycemekolle]

Life saver. Am working on a solution for my company and I have the same issues. Files coming from different units have different number of blank columns in them. There are also a number of blank rows at the top which isnt the same for all the files. I will apply this same technique and hopefully it will work.

[jimfitch]

Thanks, Mike, for 2 terrific solutions to the original problem. Your original solution works great! Loved the video & learning Bill Szysz's solution that gets the job done in 2 lines of code. Normally, I would implement the more efficient solution, but I'm sticking with your original solution b/c it works perfectly, I've already beyond it in development to replace the old application, and I don't have big data concerns. But, if I encounter this need again, I'll remember that there are 2 solutions. Thanks!

[djl]

Nice! You're a power query sharp shooter Mike!

[jameszhou]

Going through MSPTDA sessions made it so easy to understand what you are doing. Otherwise, it will be too much to comprehend. Thanks a ton.

[abdulhaseeb]

This video is so amazing I have already watched it 3 times and It still feels good to see something so magical. You are a true wizard Thanks Mike.

[ralphdeuster]

Thanks Mike and Bill for this great trick! As always: Thumbs up!

[janithrukshan]

This is awesome mate, it has solved a bag of problems. Very happy!!!

[manideepak]

No words for your contribution
Thank you mike

[naveen]

Wow!!! Another Great Tutorial From my Great Teacher..THANKS a

[johncatsicas]

Thank you Mike for your imparting your knowledge - what more can we lesser mortals say about your generosity. God bless you and your family - stay safe

[pravinshingadia]

Thank you Mike - awesome video. Helped me with a problem today!