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

Download Excel Start File: excelisfun.net/files/EMT1660S...
Download Text Files: people.highline.edu/mgirvin/Y...
Download Excel Finished File: people.highline.edu/mgirvin/Y...
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

[@tejamarneni]

ExcelIsFun hi Mike, I have a question.couple of months ago I had an interview and one of the question was what optimization tools in excel do you use? I don’t know anything about those optimization tool. Can you please explain what are the optimization tools in excel ?

[@ExcelInstructor]

13:18 - Yes Yes YES Tripple yes to the power of quadroople YES. i need to visualize, when isee the results its way easier me. that's why dynamic arreys made my day easier!

[@ExcelInstructor]

20:20 that conused me. How variable City gets its data? i mean in prev table its called Name so how the variable knows from where to take the correct city? I'm confused here alot.

[@bamakaze]

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

[@dirceushimizuschaack1742]

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

[@excelisfun]

Yes, there are so many great tricks in one video : ) Glad you like it, Dirceu!!!

[@abdulhaseeb8027]

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.

[@stephanweaver1960]

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

[@reng7777]

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

[@excelisfun]

Always glad to help, RENE!!!!! Thank you for your support with your "Master Mind" comments and thumbs ups, and of course your Sub : ) Bill Szysz is the Master Mind, I am just a good story teller : )

[@EricGiroux]

@@excelisfun Hi Mike, "I am just a good story teller : )" You are far more than that, your ability to syntethis complex concepts in Excel is absolutely stunning and more than that, you generously and enthusiastically share it to the commmunity . One thing for sure, you and Bill are quite a team than bring knowleage and pleasure to use Excel. Thanks to both of you, Mike Girvin and Bill Szysz! :-)

[@excelisfun]

@@EricGiroux You are welcome, Eric! Always glad to bring free and fun education to the world : )

[@bravucod]

The learning never ends!

[@excelisfun]

Nor does the fun : ) Thanks for stopping by in the comments, Daniel : )

[@bravucod]

@@excelisfun I'm a judge fan of your content. I'm so much more efficient thanks to you

[@mattschoular8844]

Perfect timing! I was starting to dabble with Custom functions this week. Thanks Mike

[@excelisfun]

Yes!!!! Glad it is perfect timing, Matt!

[@ralphdeuster58]

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

[@excelisfun]

Go Team!!!!!

[@scfo]

Wow. Absolutely stunned. Incredible Mike 👍

[@simfinso858]

This is Advance Excel for me.Great solution indeed.I can only Combine multiple sheet.then go to+ select blank & delete.

[@jerrydellasala7643]

One of the best, most practical on PQ!

[@excelisfun]

: ) : )

[@emmanuelagbo218]

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

[@johncatsicas101]

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

[@excelisfun]

Go Team!!!!!! Alwasy glad to help, John!!

[@pravinshingadia7337]

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

[@Dexter801]

Wow What a course!! Thank You Mike, truly grateful to be able learn how to Master Excel via your channel

[@excelisfun]

You are welcome, Derek!!!

[@manideepak6820]

No words for your contribution Thank you mike

[@excelisfun]

Your words are great. Thanks : )

[@jameszhou162]

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.

[@excelisfun]

Yes, that MSPTDA is a great help : ) Glad you like the MSPTDA and this video, James!

[@10ozGold]

This is a beauty! Saves so much time. Excellent video Mike. You rock!

[@excelisfun]

Glad it helps : )

[@janithrukshan]

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

[@excelisfun]

Solving "bags of problems" is great!!!! Glad to help, Janith : )

[@moshikoaivi]

Wow it is really great Mike ! Each time I learn new things and I'm shocked how amazing PQ is ! Thanks to Bil and you it is so helpful the simple way we can visualize the steps. It makes sense for what we are doing ! Thank you ♡

[@excelisfun]

Glad you enjoyed it!

[@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!

[@excelisfun]

You are welcome, Jim! Great to hear that the RemoveBlankRows^2 and Transpose^2 method works great!

[@roycemekolle7334]

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.

[@naveen3406]

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

[@excelisfun]

You are welcome a Ton, naveen!!!!

[@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!

[@excelisfun]

Yes, as a video creator you know how much work it takes. It is all about story. Bill Szysz gave me the code, but then I had to invent a story to tell : ) This video took me 50 hours to make. Go Team: MF Wong, Bill Szysz and so many others : )

[@wmfexcel]

ExcelIsFun you are amazing 😉

[@henrytran4782]

Many thanks, master Mike. You are awesome.

[@excelisfun]

Glad you like the story I told about Bill Szysz Code : )

[@chrism9037]

Very helpful, thank you Mike!

[@excelisfun]

You are "M Code" welcome, Chris!!!! Great to be on a good Team with you AND Bill Szysz : )

[@avivkoren527]

Great video Mike thanks a lot

[@SyedMuzammilMahasanShahi]

Thanks amazing Mike for this EXCELlent video.

[@excelisfun]

You are welcome for the EXCELlent video, Syed MM!!!

[@nigelbarrett8973]

Thanks Mike, great video. This is going to be very useful.

[@djl8710]

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

[@excelisfun]

PQ Sharp Shooting is easy when Bill Szysz is around : )

[@ThermalWarrior]

Great video, thanks Mike!

[@excelisfun]

You are welcome, Graham!!!

[@beautyjournal217]

You work so hard !!! Thank you !!

[@excelisfun]

How could you tell? This video took about 50 hours... It is fun to try and make good and fun story's about the cool formulas Bill Szysz comes up with! You are of course welcome, Awaking Beauty!!!

[@beautyjournal217]

ExcelIsFun oh I know you work hard! All your videos, efforts and knowledge are much appreciated !

[@kebincui]

Super! Thanks Mike👍

[@excelisfun]

You bet!

[@serigamel]

wow. thanks so much for this! it has helped me a lot in my work!

[@excelisfun]

You are welcome, Mariah!!

[@ennykraft]

Thank you for another fantastic video! I hope you and your loved ones are well. I had to write some M Code für the first time yesterday and it took some time getting used to the syntax. Some kind soul from Mr Excel's forum helped me a lot with one step I couldn't figure out. This is definitely I would like to learn more about. Sometimes it's easier to figure out how to transform the data instead of explaining over and over again how the data we get should look like. Although in my problem it was the database that didn't export a list in a format that was usable and the database people told me they couldn't change it.

[@excelisfun]

I LOVE the Mr Excel Message Board! It is my favorite place to ask questions. Who was it that answered your question? Also, if you want to learn about M Code, my MSPTDA class teaches a lot about it.

[@ennykraft]

@@excelisfun The member was sandy666. I'm so grateful for the help (s)he gave me. I can do transformations and calculations with the PQ menu but have never written M code up to now. Power Query is wonderful for transforming data for database publishing in InDesign. I am also so glad we have the new dynamic array functions. Without them the further transformation of the data would have been much more complicated. I will definitely watch that class. Learning new things is wonderful and you make it much easier to understand.

[@ennykraft]

@@excelisfun And now Bill Szysz has written another code for the problem and it uses the (t as table) => you showed in your video. It'll be great to try his method.

[@paspuggie48]

Love it Mike. Thank you so much 😉

[@excelisfun]

You are welcome so much, Paul!!!

[@josephcho2350]

Awesome thank you excel master

[@HoppiHopp]

Such beautiful code!

[@excelisfun]

Glad you like it, Hoppi! Lucky we have Bill Szysz on the Team : )

[@kiwikiow]

Another great video. Enjoy watching very much. You're master. Thank you Mike :)

[@excelisfun]

Master of story telling, maybe. But Bill Szysz is the Master of M Code : ) ... P.S. The Ghost has not been around in a while. I wonder where the Ghost went? ; )

[@kiwikiow]

@@excelisfun You and Bill are masters. I guess the ghost is staying home 🏠

[@adrianoschwenkberg6773]

Great stuff, great tutorial

[@excelisfun]

Glad you like it!!!!

[@darrylmorgan]

Boom!Wicked Tutorial,Love The Trick To Visualize The M CODE Function Simply Awesome..Thank You Mike :-)

[@excelisfun]

Glad you like that visualization trick! I do not know what I would do without it : )

[@Softwaretrain]

Fantastic, great playing with M codes, now M is fun with Mike.

[@excelisfun]

M is Fun, I like that!

[@johnborg6005]

You make it look so easy : ) : ) I enjoyed watching you and wish i was only as good!!! But thanks mike. Loved it.

[@excelisfun]

The final story only looks easy. The production behind it is A LOT!!! Glad you loved it, Formula Guy John Borg : )

[@johnborg5419]

@@excelisfun I can Imagine that the video with all the editing takes alot of time!!! that's why all of us followers should appreciate what you do. God Bless you Mike. : ) ; )

[@JoshuaDHarvey]

Great video, thank you!

[@excelisfun]

You are welcome, Joshua : )

[@nimrodzik1]

Great video Mike. Thank You. And of course respect for our PQ magician Bill :)

[@excelisfun]

Glad you like it, nimrodzik : )

[@excelwithhemantjain7338]

Simply Awesome 👌

[@excelisfun]

Glad you like it!!!

[@wayneedmondson1065]

Hi Mike. This video is so awesome! When I first viewed it last year, I was totally lost. Now, it makes complete sense and I can follow along and build both solutions to completion. Nice to feel some progress on the journey. As always, thanks for the great lessons and tutorials that help me move forward every day :)) Go Team!! Thumbs up!! - PS - Kudos also to Bill Szysz for his clever approach!!

[@excelisfun]

I love to hear this!!!!! Awesome : ) And, it is great to have the smartest Power Query guy I know, Bill Szysz on the Team. Go Team!!!!

[@excelisfun]

This video does a good job of showing many important M Code tricks. A good video to bookmark. Go Team!!!!!

[@wayneedmondson1065]

@@excelisfun So true! I learn something new with each replay. You packed tons of value into this video.. definitely a keeper to view again and again in the future. Thumbs up!! Go Team :))

[@masterof]

👍 x 100!! Thank you! This one helps my daily Excel PQ life!

[@excelisfun]

Yes!!!!!! Love that it helps : )

[@masterof]

@@excelisfun When a new query is referencing to another query, I use "Source = ref_query_name". That ref_query_name seems to be static, or is there a way to use a variable? Does something like INDIRECT() for use in "source" exists? Many thanks...

[@EricGiroux]

Hi Mike, Thanks a lot. I'm always amazed how M code can be so flexible. I must confess that I will have to re-watch some part of the video. :-). When I saw you type nested function I did recognize Bill's signature. 😉

[@excelisfun]

Almost all of my videos are intended to be watched more than once. This is because I do not just show solution, but I also show whys. You are welcome a lot, Eric!

[@suki9860]

Thanks very much, Mike! Excellent, very useful tutorial, with plenty of side hints and tips! Please can you give some inspiration for where there are Col's 1 to 5 in 2 tables and there are only three col's in the last table i.e. In this last table, data shown in the previous tables as Col 2 data is given as Col1, Col3 data is given as Col2 and Col5 data is given as Col 3. Many thanks!

[@simonbarker2603]

Awesome Mike.

[@excelisfun]

Glad it is awesome for you, Simon!! Lucky we have Bill Szysz around : )

[@gduque26]

Awesome video as always!! It would be good to have a short one to clarify the uses of “each” within Tables and Lists functions.

[@excelisfun]

Well, I sort of have that in my MSPTDA class. Video #9. But I will consider it in the future.

[@mmunozidata]

GREAT, AMAZING

[@excelisfun]

Glad it helps!

[@hosseinhosseinpoor9561]

خیلی عالی

[@ArgenisChaffardet]

Pretty awesome 👏🏻

[@excelisfun]

Glad it is awesome for you, Argenis : )

[@ashoksahu9546]

Waooo... excellent video sir.

[@excelisfun]

Glad you like it, Ashok!!!

[@msmith3090]

Excellent isn't a superlative enough word - just amazing! Thank you for the annotations, the highlighting, the arrows, the verbal explanations, and everything else that helps those of us who are learning from you to follow what you are doing. I know my way around Power Query reasonably well, but I have to pause the video frequently to keep up with you. I frequently do each step after you. It helps me understand better what is happening. How long did it take me to follow your 22 minute video? about 45 minutes! Sometimes I do feel like your videos needs a warning though - "Some sequences shortened" ;-) And I think Mr. Szysz should be known as "The Optimizer". It seems to me that he improves everything he touches! Thanks again to both of you for all your help learning Power Query!

[@excelisfun]

45 minutes for a 22 minute video is quite fast. With the medium of video and the rewind button, my intent in creation of these lessons is that people pause, work, unpause, rewind and so on. This is how I teach, this is also how I learn when I take video classes from Marco Russo, Chris Webb and others. For some of my more concept packed videos, like MSPTDA class, a two hour video should take you a minimum of 10 hours. What makes my videos different, is that I often give you the full story with all the concepts, downloadable workbooks, notes, homework problems and so on. My videos are different than most content at KZbin, which usually focuses on quick fixes, incomplete stories and marketing prowess. Analytics show that my videos are much less popular because most people do not want the whole story; what they want is a quick fix, a solution without the whys. But no worries, I keep posting these for the small crowd that wants the full story. I think you mean by this: "Sometimes I do feel like your videos needs a warning though - "Some sequences shortened" ;-)", that editing is done to shorten the steps? I like your Bill Szysz nickname: "The Optimizer". I can see him, as a Marvel Super Hero. With Cap of course : )

[@vida1719]

Brilliant solution for messy data. However, sometimes reality is more complex - some of our suppliers not only add extra columns, but also change header names in their weekly data files, so we have to cleanse them manually. One time even file format was different.

[@excelisfun]

That is totally true. Jim, who asked this question said he was doing this with VBA, but was switching to Power Query, and he said it worked. He must have some diligent data entry people ; ) But you are right, when the data stored in a structured system, it is mayhem trying to get it into a form that we can analyze : (

[@rrrprogram8667]

Brilliant one mike... I would go with the first trick.... 2nd trick could sometime be tricky as we cannot assume that empty cols will always be named as "column"... But as always this is great learning

[@excelisfun]

Yes, that has to be an assumption. But it can be a safe assumption because the custom of blank columns in Excel Data has been around for a long time.

[@knikl]

Another great video, thank you. M for Magnifique

[@excelisfun]

You are welcome for the stroy telling about the Bill Szysz M : )

[@antonXPS]

Cool, advanced

[@excelisfun]

Glad it is cool for you, Anton!!!

[@krn14242]

Wow, thanks Mike. Once it’s setup I guess it would be easy. Lol.

[@excelisfun]

Yes, it is like anything worth doing ; )

[@MalinaC]

Thanks for PQ fun! :) Thumbs up! :)

[@excelisfun]

You are welcome, Teammate !!!

[@GeertDelmulle]

Holy smokes! WOW! Now that is Advanced PQ-M right there! I knew it as soon as you mentioned Bill Szysz... :-) This video is gonna take multiple views in order to fully embed the knowledge... Thanks so much for compiling this lesson! (y)

[@excelisfun]

I just can't wait... : ) : ) : ) : ) for the secret debut... soon : )

[@excelisfun]

Yes, Go Team!!!

[@GeertDelmulle]

Mike, can you check on the profile picture on the ... channel? Is it B&W or color? I see it both ways, depending on the browser. (?) My artist was very excited so we finished it... Thx!

[@GeertDelmulle]

My next goal is the intended video. My notes are ready. Now rehearsing the scenario and trying to make my first video of this kind... I’m sure I’ll mess up so many things, but hey: it’s triple the fun!... :-)

[@excelisfun]

@@GeertDelmulle I looked the other day and I saw nothing, I can't wait to go and look now. I ' ll report back.

[@DougHExcel]

The Table [dot] M code is amazing!

[@excelisfun]

M Code is amazing (so is Bill Szysz : ) )

[@alz7405]

Wow! 😍

[@excelisfun]

Glad you like it!!

[@HusseinKorish]

Thumbs Up 👍👍👍

[@excelisfun]

Glad you like it, Hussein : )

[@ajaymekale9207]

Thanks

[@excelisfun]

You are welcome!!! Thanks for the kind donation, Ajay!!!!

[@ajaymekale9207]

@@excelisfun Thank you Sir

[@excelisfun]

@@ajaymekale9207 : ) : ) : ) : ) Go Team!!!!!

[@petermyran4986]

H Mike - another excellent PQ learning session!! I am so grateful. Question: I have used the framework of what you presented here to do a similar exercise using multiple sheets and multiple data regions to transform into a proper data set. I am also abstracting as much as I can by invoking custom functions to return transformed tables, i.e. doing the work in steps. As a life long C languages (C, C++, C#, etc) guy I find myself always looking for a way to iterate over a collection - a for loop / foreach sort of thing. I probably should not be thinking in this mode to solve things in PQ. But can you tell me how I'd do a mult-step process like in an abstract way: #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"I/E Categories", type text}, {"Number of Attendees", Int64.Type}, {"Number of Staff", Int64.Type}, {"Weekend Revenues", Int64.Type}, {"Staffing Fee", Int64.Type}, {"PG Group Revenues", Int64.Type}, {"Total Revenues", Int64.Type} }), or only slightly better: changedType = Table.TransformColumnTypes(promotedNewHeaders,{{ newColumsList{0}, type text}, {newColumsList{1}, type number}, {newColumsList{2}, type number}, {newColumsList{3}, type number}, {newColumsList{4}, type number} }), in an invoked function where I don't want to have to know about the columns of the table being passed? I hope I am being somewhat clear, if not, I'll try to explain my goal and challenge

[@AbdurRahim-ot5gp]

I highly requested you make stock control tutorial video from product table, customer table and suppliers in Microsoft Access.

[@excelisfun]

I am sorry, I don't really do Access videos any more : (

[@aabdifatahhas]

Thanks very much, Mike! Excellent, very useful tutorial, some Error in Table DataFormat.Error: File contains corrupted data. Details: [Binary]

[@jaychowdhury7251]

Thanks a ton Mike. All your videos are incredible. I have recently started learning more about Power Query, thought of giving it a try. As I can't remember so many functions and formulas. Reason for loving excel is that they give information about the formula as to what is needed to complete the formula, however, I'm waiting to see the same on power queries. Is there a check box if enabled can show the required fields once I start typing the formula in POwerQuery? I also noticed that at 10:06 of this video, at the formula bar, you are getting some fonts in blue and some in red, whereas, it doesn't reflect the same in mine? any reason for it? i m uing office 2019

[@MarioEPerez-nb7dx]

Hello there. Thank you for this tutorial. I ran into a few snags. I keep getting an expression error not recognizing the "table" name or the "GetExcelSheet" name. Also maybe its my 2016 version but when I enter code into the advanced editor it does not auto populate functions that can be used. Has anyone run into this?

[@tha2irtalib343]

Super massive...

[@excelisfun]

I like that: Super Massive : ) : ) : )

[@rjbush7955]

Hey Mike, One of my bugbears is extra blank sheets. Is there M code to get rid of? Thanks - Love your PQ's

[@excelisfun]

It would depend on circumstances, but an empty sheet probably shows up as empty table. If field names were promoted and you were appending you could filter out "Column", then remove blank rows. I guess we could also check if the table in a column named "Data" is empty with: Table.IsEmpty(_[Data]) , then filter out TRUEs.

[@yosra782]

Hey Mike, amazing master class as usual! Can XL do this magic? :) I had to lookup values (string) in a column (all cells contain text ), and write the value if the value exists

[@yosra782]

Example: List of the values: 20MEMFEE 21MEMFEE 20MIEG 20PANCR 21MIEG 21PANCR The column where I need to search these values is only has text cells, each cell contains a long text including one of the values above. Example: A1:73000-20MIEG-REG COURSE FOR John Smith A2:Membership fees Dr OZ 732000/21MEMFEE/IRMF A3:Course for Mike 730000/21PANCR/REGISTR A4:730000-20MEMFEE-JMF MEMBERSHIP DR PHIL So there is no specific order for the value in the text. The goal is to look if any of the values exist in a cell from column A, and write it in column B (B1, B2, B3, B4)

[@excelisfun]

@@yosra782 In Office 365, this formula would work: =TRANSPOSE(FILTER(List,ISNUMBER(SEARCH(List,A1))))

[@yosra782]

@@excelisfun thanks Mike! I am going to try it! :)

[@yosra782]

WOooohhhhW

[@ArtionKarreci]

Hi there, Complimenti for your excellent teaching classes! may ask you if there is a way to work with data that in a field named 'Description' contains line feed character? Excel interprets it as a new line so the data is misaligned. I searched on the internet for solutions based on M code but I didn't find much. I think the way to go is to write a custom function. This video, and your other videos also, show the potential of PQ and M code. The better solution is to change the query that produces the data but if that's not the case, have you any suggestion on how to deal with? Thanks again for your great work sharing your knowledge.

[@excelisfun]

To remove Line Feed and replace with a Space: Maybe a Power Query Formula like this: = Table.ReplaceValue(DescriptionTable,"#(lf)"," ",Replacer.ReplaceText,{"DescriptionColumn"}) Maybe an Excel Worksheet Formula like this: =SUBSTITUTE([@Description],CHAR(10)," ")

[@ArtionKarreci]

@@excelisfun I tried the M-Code Table.ReplaceValue, as the CSV with the raw data has too many rows to process in Excel. This is the query: let Origin = Csv.Document(File.Contents("C:\Users\t400\Desktop\DM\consuntivo\M605421010328004.txt"),[Delimiter=";", Columns=42, Encoding=1252, QuoteStyle=QuoteStyle.None]), lf_replace = Table.ReplaceValue(Origin,"#(lf)"," ",Replacer.ReplaceText,{"Column35"}), t_promoteheaders = Table.PromoteHeaders(lf_replace, [PromoteAllScalars=true]) in t_promoteheaders unfortunately, it doesn't work as the data in the first step, Origin, already is misaligned; the text in Column35 for the rows that contain linefeed in it has been already gone on a new row. I also found this solution (esee.ly/WuYJTE) but couldn't understand it as much to apply it to my case.

[@ArtionKarreci]

it turns out that it was simpler than imagined (esee.ly/daZLIO) thanks a lot for your efforts. Maybe this case and solutions can help others.

[@rabeez20]

Hello Mike, very helpful tutorial. I am running into an error message "The name 'GetExcelSeet wasn't recognized. Make sure it 's spelled correctly." After I copy and paste steps into new query and click Done. Any insight how I can solve for that?

[@singhalmonica]

Pls guide me how to get .xls file by same way. I got error, while doing so. Pls help

[@erictsang789]

I have problem with importing different excel files , with different columns in one folder. some of the files have 4 columns, and some have only 2 columns. When i combine then error prompt and ünknown fields altered. how can i solve it ?

[@excelisfun]

Appending does not work unless the columns have the same name.

[@brianxyz]

Are the column names always the same?

[@erictsang789]

Dear Brian The columns most of them are the same, only 1 is different. file 1 - Sales and Date file 2 - Sales and date and Sales IN charge Like that Eric

[@dravetch]

Excel.Workbook doesn't work with csv or txt files. What can you use instead? Thank you.

[@excelisfun]

Rather than Excel.Workbook, use Csv.Documnet, like : Csv.Document(Parameter1,[Delimiter=" ", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None])

[@armaankadiwala6049]

Hi sir I need a help I got a task from my boss can you help me to complete that task ...

[@HachiAdachi]

Hey, Mike, I can't remember if you have mentioned this in your past videos, but setting the second parameter (delayTypes) in Excel.Workbook() function to "true" may also improve the performance when dealing with large datasets. Chris Webb has written an article last year on this topic (blog.crossjoin.co.uk/2019/02/02/excel-workbook-and-the-delaytypes-option-in-power-query-power-bi/). I'm curious to know if this would further reduce the processing time of your 4-million-row dataset.

[@excelisfun]

That is an awesome tip. Thank you for the true = faster from Chris Webb. I look forward to going and reading Chris Webb's article. Thanks, as always, Hachi : )

[@excelisfun]

Just read it. Very helpful : )

[@OakleyTurvey]

FunTastic new tricks. But (and please remove this comment if it distracts from the content of your post): In the 'real world' your data should not be coming to you for processing in .xlsx format. This is for several reasons, here are a couple: 1. It is limited to just over 1m records per sheet which encourages bad practice of spreading data across sheets as a work-around when people have more data than this. 2. It is actually not a file format, but an archive format. .xlsx has multiple files packaged within it. This means it is very inefficient to process as it first has to be un-packaged. If the data has been output from a database by running a query, then the person who has done so is not helping you if they provide large data sets in .xsx. Now if you had got the data as a .csv: Load files from folder - Replace .csv with nothing in the Name column (as you did with .xlsx) - Select Name and Content and Remove other columns - Combine Files Button - (this will automatically append to the correct named column) - Select just the columns you want (i.e. not the blank ones) and Remove other columns - Now go to the Date column and Remove Empty. BOOM. Job done. Run a timed test on your 4m records with them coming from .xlsx and .csv as a time comparison to see the difference. It should be interesting.

[@excelisfun]

Remove your comment!?!?!? That would be Anti-Teammate-Ship-Ness, if I can make up a fun word ; ) Yes, you are very correct. .xlsx is not an efficient format. And your comment is very helpful. You are right that in the "real world", we should have text files to remove data from one system to another. But in the working world ".xlsx" is prevalent and has been prevalent for the past 30 years for many reasons. For example, the Enterprise Resource Management System that Highline College uses, the user interface ONLY exports as ".xlsx"!?!?!?! It is a crazy setting. It goes against the past 30 years of precedent where text files are the default for most data export. But we have to deal with it : ( Some entities opt for the inefficient Excel File Format as a way to create, gather and share data. More to the point, however, if you read Jim's whole comment, his customers submit files, and as he said - no matter how much they try to train people, they still get a lot of Excel files from many different constituents, some with blank rows, some with blank columns, and in the past they used VBA and Access to solve this. But Jim said he wanted to try Power Query rather than VBA and Access. On this same point, over past 30 years removing blank rows and blank columns and consolidating data into a proper data set with Excel files has been a common task. Most of the time it is with manual data in Excel that is not being generated from a database. Alos, Blank Columns in data sets was very common, especially in the early years of spreadsheets, and then even later when we had PivotTables and other great tools, this custom persists. In the past, most Excel solutions to get ride of Blanks rows and columns during an Excel Import Event were done with VBA, but now with Power Query, and it may be easier : ) As for the 4 million rows, I should have said in the video, that I just did that for timing. As with Jim's case, the data is small data. And also most situations like this are not using Big Data. Thanks for your useful comment (as always), Oakley! Go Team!!

[@OakleyTurvey]

@@excelisfun Mateship is very much a word in the Australian Military, so Team-mateship if not a word then it should be. My comment was made because I find that very often my customers are a fan of a particular tool (normally because of familiarity), and they try to use that tool to fit every situation, rather than reaching into their toolbox to get the one that makes the job easy. While I accept that your ERM system may only currently output to .xlsx, that is simply because it is badly set-up, not because it is impossible for it to be changed to do so. You just need to find out who is in a position to demand that change, and who influences them, and target that person with your reason for change. (Good luck with that one BTW.) I have had a similar issue with some customers here in UK who use SAP as a database, getting Dates output in the text files in German format, simply because the programmers didn't know how to output in Local format, so they claimed that was just what it does... I am always telling my Advanced course delegates to think of what they are learning, be it Power Query, Power BI, or VBA to regard them as 'a' tool and not 'the' tool for doing the job. Many of the problems I am asked to fix for customers come from them over-thinking the problem, or trying to make it fit their instinctive, first thought of solution. I always try to think of the 'lazy mans' solution to the problem; i.e. what would make this NOT a problem...? Please continue your FUN-tastic work and being such an inspiration to so many.

[@excelisfun]

@@OakleyTurvey LOVE the lazy-mans solution, also know as efficient solution ; ) If only the millions of data people over the last 30 years had good teachers like you, so that they would not just use the tool they are familiar with. But just as with Jim's situation, and so many others, it doesn't seem to always work that way. BTW, our system at Highline was designed because people complained about the last system always giving them CSV files, and so that is why it was changed in the new system they just bought to export as "xlsx". I was not part the decision making when they planned, designed and implemented the new ERP system...

[@excelisfun]

@@OakleyTurvey I look forward to more Oakley wisdom in the comments for our TeamMateShip-Ness : )

[@kingworld2002]

believe me, I did not understand anythings:)

[@abdullahibishar3941]

Need education

[@excelisfun]

I got the education for you, Abdullahi!!! Here is my 2 minute video about finding just the video or class you want: kzbin.info/www/bejne/omKQYpSMnKunnpY