Every Monday, I spend hours copying and pasting data from five different software exports into one master sheet. I’ve heard Power Query can "record" these cleaning steps. How do I set up a folder-based connection that automatically formats dates and removes duplicates every time I drop a new file into the directory?
3 answers
Power Query is essentially an ETL tool built into Excel. To automate your workflow, go to Data > Get Data > From File > From Folder. Once connected, click "Transform Data." Any steps you take—like Remove Rows > Remove Duplicates or Change Type for dates—are recorded in the "Applied Steps" pane. When you add a new CSV to that folder next Monday, you simply hit "Refresh" in Excel, and Power Query replays those exact steps on the new data. It’s significantly more robust than VBA macros because it doesn't break if the data structure has slight variations or extra empty rows at the bottom.
Are the headers in all five exports identical? If the column names vary even by a single space, Power Query might throw an error during the "Combine Files" step unless you use a custom M-code function.
Once you set up the folder connection, make sure you "Close and Load To" a Pivot Table directly. This saves memory and keeps your workbook from getting too bulky with raw data.
I agree with Lisa. Loading to the Data Model instead of a worksheet is the best way to handle large datasets. It’s what transformed my reporting from hours to seconds.
James, the headers are mostly the same, but one system uses 'Date' and another uses 'Transaction_Date'. How do I write a simple 'M' script to normalize these column names automatically so the append function doesn't create two separate columns?