Combine ERP Reports and Remove Duplicate Rows in Power Query
Question details
The user needs to consolidate daily ERP shipping reports containing overlapping data and filter out duplicate line items based on Delivery and Material columns before loading the data into Power BI.
- Product
- Excel / Power BI
- Device & OS
- not provided
- Scenario
- Consolidating daily ERP shipping reports that have overlapping time frames and duplicate records.
- Observed behavior
- Duplicate line items are generated across the overlapping daily reports, requiring precise deduplication based on specific identifier columns before the data can be analyzed.
Ensure all of your daily ERP reports are stored in a single, dedicated local folder or a SharePoint folder, and verify that they share the exact same column structure.
Use 'From Folder' Connector and Remove Duplicates Feature
This method automatically appends all files in a designated folder and uses specific identifier columns to filter out overlapping entries.
Power Query allows you to treat a folder as a data source. When you append the overlapping reports, you can specify exactly which columns determine a unique record to eliminate duplicates safely.
Open Excel or Power BI, navigate to the Data tab, select 'Get Data' > 'From File' > 'From Folder' (or 'From SharePoint Folder'), and select the directory containing your ERP reports.
Click 'Combine & Transform Data' in the preview window to append all the daily reports into a single Power Query Editor session.
In the Power Query Editor, hold down the Ctrl key and click the column headers for both 'Delivery' and 'Material' to highlight them simultaneously.
Right-click one of the highlighted column headers and select 'Remove Duplicates'. Alternatively, navigate to the Home tab and click 'Remove Rows' > 'Remove Duplicates'.
Click 'Close & Load To...' and choose to load the query directly into your Power BI dashboard or as a Data Model connection to avoid creating unnecessary intermediary sheets.
Need a Lightweight Alternative for Data Processing? Try WPS Office
While Power Query is a specific Microsoft tool for automated dataflows, if you need a fast, free, and lightweight solution to manually review combined reports and manage duplicate rows, WPS Office is an excellent choice. It provides seamless compatibility and robust data tools without the heavy subscription costs.
- 1. Open your report: Launch WPS Spreadsheet and open your combined ERP report.
- 2. Access data tools: Navigate to the Data tab located on the top ribbon.
- 3. Remove duplicates: Click on 'Highlight Duplicates' or 'Remove Duplicates', select the Delivery and Material columns, and click OK to clean your dataset.
Frequently Asked Questions
Can I automate the refresh of my combined ERP reports?
Yes. If you load your data into Power BI or utilize a Power BI Dataflow, you can set up scheduled refreshes. You can also use Power Automate to trigger a data refresh automatically whenever a new daily report is dropped into your SharePoint folder.
What happens if the daily reports have different column names?
Power Query relies on matching column names to append files cleanly. If the column headers differ across your daily reports, you will need to add a transformation step in Power Query to uniformly rename the headers before executing the combine step.
Why should I select both Delivery and Material columns before removing duplicates?
Selecting both columns simultaneously ensures that Power Query evaluates the unique combination of these two fields. If you only select one, you might accidentally delete valid line items that simply share the same Delivery ID but have different Materials.
Should I load the combined query into an Excel sheet or Power BI?
If your final goal is a Power BI dashboard, it is highly recommended to load the query directly into Power BI or build a Dataflow. Outputting millions of rows into an Excel workbook first creates an unnecessary and bloated intermediary file.
