Power Query Problems

How to Organize Schedule Data into Sorted Job Columns with Power Query in WPS Office Excel

Natalie Taylor Nov 13, 2026 869 views

Question details

The user needs to transform schedule entries into a normalized layout with one column per job, sorted alphabetically, while retaining the dates or days on which each job occurred.

How to Organize Schedule Data into Sorted Job Columns with Power Query in WPS Office Excel

Product: WPS Office Excel
Device & OS: not provided
Scenario: A user has scheduling data in a spreadsheet and wants to reorganize it for easier reading and analysis by job instead of by entry type.
Observed behavior: The desired output is a query-generated table with one column per job, sorted alphabetically, and dates or days retained for each occurrence.

Before you start

Make sure your schedule range has a clear header row and consistent date, day, or job entries. Convert the source range to an Excel table before loading it into Power Query so the columns are recognized automatically.

Solution 1Recommended

Transform schedule entries into sorted job columns using Power Query

Use this method when you need to convert a spreadsheet schedule into a clean table with one column per job, sorted alphabetically and tied to each date or day.

Power Query works best when you first normalize the table. Unpivoting changes multiple job columns into Attribute and Value rows, so you can clean labels, remove placeholders, and sort them before pivoting back into separate columns.

Keep the date/day column as a row anchor during the unpivot and pivot steps. This preserves the relationship between the schedule date and each job occurrence.

Convert the schedule range to a table

Select a cell in the schedule range, go to Insert > Table, confirm the range and that My table has headers is checked, then click OK.

Load the table into Power Query

Select the table, go to Data > From Table/Range. The Power Query Editor opens with the schedule columns.

Clean placeholder values

Select the schedule entry columns, go to Home > Replace Values, and replace placeholder values such as "Labor Day" with null or remove them.

Unpivot schedule columns

Select the date or day column, then go to Transform > Unpivot Other Columns. The job labels move into an Attribute column and the values into a Value column.

Clean and sort job labels

Use Transform > Split Column or Home > Replace Values on the Attribute column to isolate the job names, remove blanks, then select the job field and use Home > Sort Ascending.

Pivot job labels into columns

Select the cleaned job label column, go to Transform > Pivot Column, choose the Value column, and select Don't Aggregate if available.

Load and refresh the result

Click Home > Close & Load to return the completed table to the worksheet. After adding new schedule data, click Data > Refresh All.

Transform schedule entries into sorted job columns using Power Query

Keep job labels consistent: If job labels include extra text such as shift codes or dates, split them before pivoting so each job name becomes a unique column.

Power Query in WPS Office

Create sorted schedule columns with Power Query in WPS Office Excel

WPS Office Excel gives you the Power Query tools to unpivot, clean, sort, and pivot schedule data into one column per job while keeping dates and days aligned. The result stays compatible with Microsoft Excel files and refreshes with one click.

1. Open the schedule workbook in WPS Office Excel: Launch WPS Office, open the Excel file, and confirm the schedule range is ready to convert into a table.
2. Create an Excel table: Select the schedule range, click Insert > Table, and enable My table has headers.
3. Open Power Query Editor: Go to Data > From Table/Range to load the table into Power Query Editor.
4. Reshape and load the job columns: Unpivot, clean, sort, and pivot the job fields in Power Query, then click Home > Close & Load.
5. Refresh future schedule changes: After updating the source table, click Data > Refresh All to regenerate the sorted job schedule.

Built-in Power Query tools reshape schedule data without manual copyingSorts job names alphabetically and keeps date/day rows alignedRefresh the query after schedule updates instead of rebuilding the reportOpens and saves .xlsx workbooks for Microsoft Excel compatibilityFamiliar ribbon interface makes migration from Microsoft Office easy

microsoft office alternative - wps office

Frequently Asked Questions

Can I refresh the Power Query result when I add new schedule data?

Yes. Keep the new rows inside the source Excel table, then go to Data > Refresh All in WPS Office Excel to update the output with the new dates and jobs.

Why does my pivot step create duplicate or blank job columns?

Duplicate or blank columns usually mean placeholder values or extra text remain in the job label column. In Power Query Editor, replace placeholders with null, filter out blanks, and split the Attribute column before pivoting.

What is the purpose of unpivoting schedule columns first?

Unpivoting moves multiple schedule columns into Attribute and Value rows. That makes it easier to clean job names, sort them alphabetically, and then pivot them into one column per job without losing date information.

Will this method keep the original date or day column in the final table?

Yes. Select the date or day column before using Unpivot Other Columns so it remains as a row identifier and stays aligned with the job columns after pivoting.

Does WPS Office support Power Query files created in Microsoft Excel?

WPS Office is compatible with common .xlsx and Excel workbook formats. Open the file in WPS Office Excel and use the Data tab to review, edit, or refresh Power Query outputs.

Excel & Data Problems