Trim and Clean .. how do you know when to?

[PBInonpro]

Not by choice, but we utilize a lot of Excel Spreadsheets that are formatted. When we go into excel we would check each column's data to see if there were extra characters (undefined, tabs, spaces, etc) and clean from there. 

not that I transitioned over to Power BI I noticed it isn't easy to check data the same way to determine if it needs to be cleaned/trimmed; should it be a blanket rule to clean/trim all excel spreadsheets? And further, what is the difference between trim and clean!!?? 

[Daryl-Lynch-Bzy · Accepted solution]

Hi @PBInonpro - using both is recommended if you are importing Excel files.  Please refer to the Power Query guidance to see the difference between removing blank spaces at beginning and end (trim) and removing special characters (clean).  
Text.Clean - PowerQuery M | Microsoft Learn
Text.Trim - PowerQuery M | Microsoft Learn

Note this may not remove all special characters like #?|.