How to Convert an ERP Sales Order Export Into Excel Tables in WPS Office
Question details
The user needs to transform a print-oriented ERP sales order export into separate structured Excel tables for order headers and line items using Power Query.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- An ERP sales order export contains mixed order header and detail lines, and the source needs cleaning before the rows can be turned into reliable Excel tables.
- Observed behavior
- The ERP export contains blank rows, repeated or missing order numbers, and mixed header and line-item rows that cannot be used directly as structured Excel tables.
Open WPS Office Spreadsheets and confirm that your ERP export is saved as a supported file such as .xlsx, .xls, or .csv. Keep the original export unchanged so Power Query can refresh from the same source when new orders arrive.
Use Power Query to Clean the ERP Export and Split It into Header and Line-Item Tables
Best for recurring ERP exports because it creates repeatable queries that refresh without changing the original file.
Power Query shapes the source in memory. It removes empty rows, fills missing order numbers, and separates order header rows from line-item rows before loading each set as a separate Excel table.
Open WPS Office Spreadsheets, click the Data tab, and select Get Data > From File to load your ERP export into Power Query.
In Power Query, select the relevant columns, remove completely blank rows, and use the Fill Down command on the order number column so every detail line inherits its order number.
Add a conditional column or filter based on the row layout, such as rows with order date or customer fields for headers and rows with product or quantity fields for line items.
Reference or duplicate the cleaned query, filter the header query for header rows, filter the line-item query for detail rows, and keep only the required columns in each.
Click Close & Load for each query and choose Table so WPS Office creates separate structured Excel tables in the workbook.

Split Smaller Exports Manually with Filters and Formulas
Useful for one-off small exports when you do not need a repeatable Power Query refresh.
Transform ERP Sales Order Exports into Separate Excel Tables in WPS Office
Use WPS Office Spreadsheets and Power Query to clean the exported report, split order headers from line items, and load two structured Excel tables that refresh from the original file.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and start a new workbook or open the workbook where you want the ERP tables.
- 2. Load the ERP export into Power Query: Click Data > Get Data > From File, choose the ERP export file, and select the worksheet that contains the sales order export.
- 3. Clean and split the data: In the Power Query Editor, remove blank rows, fill down order numbers, and create a reference query to separate header rows from line-item rows.
- 4. Load header and line-item tables: Apply the required filters to each query, choose Close & Load > Table for both, and place them in the workbook.
- 5. Refresh when new orders arrive: Save the workbook, add the new export to the same location, then click Data > Refresh All to update the tables.

Frequently Asked Questions
How do I open my ERP export in Power Query in WPS Office?
In WPS Office Spreadsheets, click the Data tab and select Get Data > From File. Then choose your ERP export file and the worksheet you want to load.
How does Power Query know which rows are order headers and which are line items?
After removing blank rows and filling down order numbers, you filter based on known header fields such as order date or customer name for headers, and product or quantity fields for line items.
Can I refresh the tables when I get a new ERP export?
Yes. If the new export is saved in the same location, open your workbook and click Data > Refresh All. WPS Office will update the queries against the new source data.
Will the original ERP export be changed?
No. Power Query loads a copy into WPS Office and shapes it in memory. The original ERP export file remains unchanged.
Can I load the result as a normal Excel table instead of a connection-only query?
Yes. In the Power Query Editor, choose Close & Load, then select Table so WPS Office places the header and line-item results as separate Excel tables in the workbook.




