Thanks Mynda and Phil, there were some really useful PQ functions that were new to me here so I will keep this video on speed dial. Also really helpful to see how to create a repeatable function. Keep up the awesome work!
@MyOnlineTrainingHub2 жыл бұрын
Our pleasure! 😊
@hectorbello21042 жыл бұрын
I was dealing with this exact issue a few months ago in one of my assignments at work. It took me ages to remove empty columns, but I never got a clean solution as the one here explained. I will certainly be using this function from now on. thanks for sharing.
@MyOnlineTrainingHub2 жыл бұрын
Great to hear you'll be able to make use of it in future, Hector 😊
@10ozGold Жыл бұрын
Brilliant Phil and Mynda!!! So good, super useful. Thank-you so much!
@MyOnlineTrainingHub Жыл бұрын
Glad you enjoyed it!
@TheOctophant2 жыл бұрын
Oh crap. I needed that Video like three weeks ago. I was struggling so bad with this
@MyOnlineTrainingHub2 жыл бұрын
Damn! Better late than never 😉
@darrylmorgan2 жыл бұрын
Hi Mynda!Another Excellent And Very Helpful Tutorial From Phil...Thank You Both :):)
@MyOnlineTrainingHub2 жыл бұрын
Glad you enjoyed it, Darryl!
@wayneedmondson10652 жыл бұрын
Great lesson! That function is a mind bender. Thanks for sharing the code. Thumbs up!!
@MyOnlineTrainingHub2 жыл бұрын
Cheers, Wayne!
@IvanCortinas_ES2 жыл бұрын
Excellent tutorial. Thank you Mynda. That's very complete!
@MyOnlineTrainingHub2 жыл бұрын
Glad you like it, Ivan!
@iankr2 жыл бұрын
Wow! Many thanks, Mynda & Phil. That's pretty heavy. I will need to have a look at that code more closely (split onto separate rows) to see what's going on! 😀
@MyOnlineTrainingHub2 жыл бұрын
Cheers, Ian!
@chrism90372 жыл бұрын
Excellent, thanks Mynda. PQ can do just about anything!
@MyOnlineTrainingHub2 жыл бұрын
So true, Chris!
@Hamish_A2 жыл бұрын
This is fantastic! Just starting my power query journey. It does make me wonder why this isn't a default behaviour/option. It seems like the power query team did half a job regarding this.
@MyOnlineTrainingHub2 жыл бұрын
Glad it was helpful, Hamish. Power Query is still being developed, so maybe that functionality will come later.
@farmerleigh6361 Жыл бұрын
Where do I place the fxRemoveBlanks function within the code when I import my data from a different excel file? I have a Source and Navigation step with this code: let Source = Excel.Workbook(File.Contents("B:\Monthly Reports Test Files\Source Data Example.xlsx"), null, true), #"Source Data Example_Sheet" = Source{[Item="Source Data Example",Kind="Sheet"]}[Data] in #"Source Data Example_Sheet"
@kebincui8 ай бұрын
This is an awesome video, thanks to Phil and Mynda👍🌹. By the way, the step of removing blank row step can be made a little bit shorter by trimming and cleaning first and then removing "" and null. This is reduce the step of just removing "", as below: = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(List.Transform(Record.FieldValues(_), each try Text.Clean(Text.Trim(_)) otherwise _), {"",null})))
@MyOnlineTrainingHub8 ай бұрын
That's a great idea! Thanks for sharing 🙏
@genechicago48062 жыл бұрын
Wow, that's an impressive formula! I started using Power Query a few months ago thanks to your videos, and this is something that I've come across often. Thank you! Question, is there a way to remove duplicate records in Power Query using multiple columns similar to the Remove Duplicates button in Excel? I've come across instances where removing duplicates using only one column doesn't yield the right answer.
@MyOnlineTrainingHub2 жыл бұрын
Sure is, Gene. Just select the columns you want considered for duplicates before clicking on the 'remove duplicates' button.
@saadnageeb46332 жыл бұрын
Wonderful , Thanks very much
@MyOnlineTrainingHub2 жыл бұрын
Welcome!
@paulcollins841 Жыл бұрын
Very good and very well explained
@MyOnlineTrainingHub Жыл бұрын
Thanks so much 🙏
@OmarAl932 жыл бұрын
Brilliant. Many thanks Mynda and Phil. I was wondering if you could share some material about VBA Macros :) A lot of exciting stuff can be explored there.
@MyOnlineTrainingHub2 жыл бұрын
Cheers, Omar! Thanks for the VBA suggestion.
@GeertDelmulle2 жыл бұрын
Bravo! This is a golden nugget! Good job! :-)
@GeertDelmulle2 жыл бұрын
PS: the logic applied reminds me of how we filter in DAX measures.
@MyOnlineTrainingHub2 жыл бұрын
Thank you! 😃
@GeertDelmulle2 жыл бұрын
@@MyOnlineTrainingHub Hey Mynda & Phil, I've simplified the code a little bit. Do you see the analogy between step 2 and 3? What do you think about it? Here it is: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], RemoveBlankRows = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(List.Transform(Record.FieldValues(_), each try Text.Clean(Text.Trim(_)) otherwise _),{"",null}))), RemoveBlankCols = Table.SelectColumns(RemoveBlankRows,List.Select(Table.ColumnNames(RemoveBlankRows), each not List.IsEmpty(List.RemoveMatchingItems(List.Transform(Table.Column(RemoveBlankRows,_), each try Text.Clean(Text.Trim(_)) otherwise _),{"",null})))) in RemoveBlankCols
@danielnoce752 жыл бұрын
This is great. I’m curious if it’s possible to do something similar for #NUM! Errors?
@philiptreacy7142 жыл бұрын
Thanks Daniel. If you have a #NUM error, when you load that to PQ it'll show as Error in the column(s). You can select the column(s) or select the entire table (CTRL+A) then right click on a column header and then on Remove Errors. Alternatively, you can check the Error and see what type it is, then do something accordingly. Please check this post to see what I mean www.myonlinetraininghub.com/iferror-in-power-query-using-try-otherwise
@JennTheWriter2 жыл бұрын
This is good and I will have to go through it several times for Excel. One question I have is that, once this is done, and you want to put it into Power BI, will the data transfer as already cleaned, or do you have to do all the cleaning over again? The cleaning part of data is really important and I need to know all of it for both Excel and Power BI, as I see it.
@JennTheWriter2 жыл бұрын
I don't suppose you have a book written specifically on cleaning data for both programs? LOL!
@MyOnlineTrainingHub2 жыл бұрын
The process is exactly the same in Power BI. Both tools use the same Power Query engine 😉
@JennTheWriter2 жыл бұрын
@@MyOnlineTrainingHub , thank you for the response. :)
@nimeshkotadia6526 Жыл бұрын
How not to remove blank columns from the table? Instead keep it as it is. I am facing problems while stacking tables because power query removes blank columns from tables on it's own when i want them to be present in the orignal tables which are read into powerquery.
@MyOnlineTrainingHub Жыл бұрын
As long as the column has a header, Power Query will bring it in.
@dp707372 жыл бұрын
Why wouldn’t you just select all and transform>clean, transform>trim to the whole thing and then just use the pivot trick?
@philiptreacy7142 жыл бұрын
Hi Derrick. Trim and Clean result in empty strings so the Unpivot/Pivot trick won't remove those rows/columns containing empty strings
@RonDavidowicz2 жыл бұрын
I need a nap now!
@MyOnlineTrainingHub2 жыл бұрын
😁 it's definitely a multi-view tutorial.
@othmanashara9382 Жыл бұрын
is there another way more easier?
@MyOnlineTrainingHub Жыл бұрын
You can try using filter to remove blanks.
@othmanashara9382 Жыл бұрын
@@MyOnlineTrainingHub yes, i did it with filter and save a lot of time. Thx
@rrrraaaacccc80 Жыл бұрын
💯👍
@MyOnlineTrainingHub Жыл бұрын
You're welcome 😊
@user-db1ux4yi6l5 ай бұрын
I gave up following the steps. Too complicated to follow.
@svitovidable Жыл бұрын
too complicated:(
@MyOnlineTrainingHub Жыл бұрын
If you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
@user-bp7yu8vk3f5 ай бұрын
very confusing explanation .i just need to remove blank rows .
@MyOnlineTrainingHub5 ай бұрын
You can do that using the 'Remove Rows' tool on the Home tab of the Power Query Ribbon.
@martinargimon7302 жыл бұрын
Hi Phil, sorry but your explanation is for advanced users ( i must assume ) since you immediately ( at the start of your video) you talk about "indexing" columns, pivoting etc, etc,.. So it is not really helping us.. I know you are an expert but we are not,. Perhaps a bit more detail explanation will help us better ?.. I've been following Mynda's channel all the time, she is definitively great, but your explanations lack the required 'additional tips' (details) to get thru' these more 'involved' features. Just a constructive criticism,.. Hope you don't mind . Thanks. martin
@MyOnlineTrainingHub2 жыл бұрын
Thanks for the feedback, Martin. Unfortunately, every video cannot start with the basics. If you want to learn Power Query then we recommend a structured approach by taking a course (www.myonlinetraininghub.com/excel-power-query-course). Our KZbin channel is a place to share ideas which can be pitched at any level. We understand that not all videos will appeal to all people. That said, perhaps we could have done more to explain the basics and will consider that in future.
@martinargimon7302 жыл бұрын
@@MyOnlineTrainingHub indeed Mynda. I should have checked your other videos before posting my comment. Sorry about this. Thanks again. Kind regards
@Hamish_A2 жыл бұрын
Just copy the steps exactly, or download the file and grab it from there. You can use it without necessarily understanding it.
@KentLundgrenLarsErik Жыл бұрын
Thanks for: = (Source as table) => let #"Removed Blank Rows" = Table.SelectRows(Source, each not List.IsEmpty(List.RemoveMatchingItems(List.Transform(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}), each try Text.Clean(Text.Trim(_)) otherwise _ ) , {""}))), #"Removed Blank Cols" = Table.SelectColumns(#"Removed Blank Rows" , List.Select(Table.ColumnNames(#"Removed Blank Rows"), each List.NonNullCount(List.Transform(Table.Column( #"Removed Blank Rows", _) , each try if Text.Clean(Text.Trim(_)) = "" then null else _ otherwise _ ) ) > 0 )) in #"Removed Blank Cols" I would never have fixed that myself!