Power Query Problems

How to Convert an ERP Sales Order Export Into Excel Tables in WPS Office

Steven ParkerSteven Parker Nov 13, 2026 870 views

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.

Convert an ERP Sales Order Export Into Excel Tables with 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.
Before you start

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.

Solution 1Recommended

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.

1
Open Power Query from the Data tab

Open WPS Office Spreadsheets, click the Data tab, and select Get Data > From File to load your ERP export into Power Query.

2
Remove blank rows and fill down order numbers

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.

3
Identify header and line-item rows

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.

4
Create separate queries for headers and 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.

5
Load the results as Excel tables

Click Close & Load for each query and choose Table so WPS Office creates separate structured Excel tables in the workbook.

Use Power Query to Clean the ERP Export and Split It into Header and Line-Item Tables
Refreshes with new data: After new ERP files are added to the same location, open Data > Refresh All to update the header and line-item tables without redoing the cleanup.
Use Power Query in WPS Office

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. 1. Open WPS Office Spreadsheets: Launch WPS Office and start a new workbook or open the workbook where you want the ERP tables.
  2. 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. 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. 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. 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.
Separates order headers and line items into two structured tables ready for reporting.Keeps the original ERP export unchanged and refreshes with new data.Works with .xlsx, .xls, and .csv files for Microsoft Excel format compatibility.Uses a familiar Power Query workflow similar to Excel for easy adoption.
microsoft office alternative - wps office

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.