I export data from our CRM and it’s a mess—dates are in different formats, names have leading spaces, and there are empty rows everywhere. How can I clean this automatically every time I drop a new CSV into my dashboard folder? I’m looking for a "set it and forget it" solution so I don't have to manually format the data every day.
3 answers
Power Query was literally built for this "ETL" (Extract, Transform, Load) process. You should use the "From Folder" connector. Point it to your CSV folder, and it will combine all files into one list. In the Power Query editor, you can right-click columns to "Trim" (remove spaces), "Clean" (remove non-printable characters), and "Change Type" with Locale to fix those date formats. The best part? When you drop a new file in that folder tomorrow, you just hit "Refresh" in your dashboard, and it applies all those same cleaning steps to the new data automatically. It's a massive time-saver.
What if the CSV columns change order? Sometimes the CRM export adds a new field or moves the "Email" column to the front. Will Power Query break, or is it smart enough to find the column by its name?
I always add a "Date Added" column in Power Query using the file's metadata. It helps me track if a specific CSV export was corrupted or is missing data.
That metadata trick is a lifesaver for auditing, Margaret. Charles, this is definitely the "set it and forget it" solution you need to stop the manual grunt work.
William, by default, Power Query looks at column positions, but you can change the code in the "Advanced Editor" to reference columns by name instead. Using Table.SelectColumns with a list of names is much safer. Charles, make sure you also look into the "Remove Errors" and "Replace Values" steps. If your CRM gives you "N/A" strings in numeric columns, Power Query can swap those for zeros in one click so your dashboard calculations don't crash.