How to Clean Messy ERP and Accounting Exports with Power Query
Question details
The user needs to transform inconsistent financial data exports from ERP and accounting systems into clean, usable formats using Power Query.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing messy financial reports containing merged cells, pre-header text, totals, disclosure notes, and mixed formatting.
- Observed behavior
- Exported data includes unusable layouts such as extra headers, blank rows, footers with disclosure text, and improperly formatted dates or currencies.
Ensure you have saved the original ERP or accounting export files (Excel or CSV) in a dedicated folder without manually altering the source data.
Create Reusable Power Query Transformations for Standard Exports
Use Power Query to automate the cleanup of financial reports by removing unnecessary rows, promoting headers, and adjusting data types.
By building a sequence of transformations in Power Query, you can handle recurring accounting exports without repeating manual cleanup. This approach standardizes messy formats like AMEX or Fidelity exports into clean, analytical tables.
Open Excel, go to the 'Data' tab, click 'Get Data', and select 'From File' to import your ERP Excel workbook or CSV export.
In the Power Query Editor, click 'Remove Rows' > 'Remove Top Rows' and enter the number of blank or title rows to delete before your actual column headers.
Click 'Use First Row as Headers' on the Home tab to set the correct column names for your dataset.
Click the dropdown arrow on your primary column and uncheck any rows containing 'Total', blank values, or disclosure notes at the bottom of the report.
Select your date and currency columns, click the data type icon in the column header, and change them to 'Date' and 'Currency' respectively to fix text-formatted numbers.
Try WPS Office for Fast and Free Data Processing
While Microsoft Excel relies on Power Query for complex automation, WPS Office provides powerful, intuitive built-in tools like Text to Columns, advanced filtering, and pivot tables to clean up accounting exports quickly and for free. It seamlessly handles both Excel workbooks and CSV files.
- 1. Download and Install: Download WPS Office from the official website and complete the free installation.
- 2. Open Your Exported Data: Launch WPS Spreadsheet and open your raw CSV or Excel export from your accounting software.
- 3. Clean the Data: Use intuitive tools like 'Text to Columns', 'Remove Duplicates', and standard 'Filter' options under the Data tab to clean your report.
Frequently Asked Questions
Why do my Excel exports from accounting software have merged cells?
Many ERP and accounting systems format Excel exports to look exactly like printed reports. This includes merging cells for centering titles and spanning addresses across multiple lines. Exporting the report to CSV format can often prevent these visual formatting issues entirely.
How do I remove disclosure text at the bottom of a financial export?
In Power Query, you can use the Text Filter option on your main column to exclude rows that 'Contain' or 'Begin With' the word 'Disclosure'. Alternatively, simply filter out null values if the disclosure text is placed in an otherwise unmapped column.
Can I automate this cleanup for monthly reports?
Yes. Once you apply the transformation steps in Power Query, you can save and load the query. The following month, simply replace the original source file with the new export in the same folder and click 'Refresh All' in the Data tab to apply all cleanup steps instantly.
