How to Split Actual and Plan Data into Columns in Power Query
Question details
The user wants to separate 'Actual' and 'Plan' data currently mixed in a single column into two distinct columns using Power Query.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transforming financial or project status data where 'Actual' and 'Plan' are recorded as row values rather than column headers.
- Observed behavior
- Actual and Plan status values are located in one column alongside corresponding amounts, requiring restructuring to display them as distinct column headings.
Before beginning, ensure your source table is loaded into the Power Query Editor and identify exactly which column contains your numerical amounts to be aggregated.
Use the Pivot Column Feature in Power Query
The correct method to transform row values like 'Actual' and 'Plan' into their own columns is by using the Pivot Column tool under the Transform tab.
When 'Actual' and 'Plan' are values in one column and another column holds the corresponding financial amounts, pivoting is necessary. Splitting a column by delimiter will not work for this layout, as that feature separates text strings within individual cells rather than restructuring table dimensions.
In the Power Query Editor, click on the column header of the status column that contains the 'Actual' and 'Plan' values to highlight it.
Navigate to the Transform tab on the top ribbon and click on 'Pivot Column'.
In the Pivot Column dialog box, select your numerical amount column from the 'Values Column' dropdown menu.
Click on 'Advanced options' to choose the appropriate aggregation method (such as Sum or Don't Aggregate) based on your data structure, then click OK.
Easily Reshape Actual and Plan Data with WPS Office
If you don't want to use complex Power Query transformations, you can easily restructure your financial data to separate Actual and Plan values into distinct columns using the intuitive Pivot Table feature in WPS Spreadsheet.
- 1. Insert a Pivot Table: Open your dataset in WPS Spreadsheet, select your entire data range, go to the Insert tab, and click on Pivot Table.
- 2. Set Your Row Labels: In the Pivot Table pane, drag your main identifier (like Project Name or Account) into the Rows area.
- 3. Create Actual and Plan Columns: Drag the status column containing your 'Actual' and 'Plan' labels into the Columns area to create separate headings.
- 4. Aggregate the Amounts: Drag your amount column into the Values area. WPS will automatically calculate and align the figures under the correct Actual and Plan columns.
Frequently Asked Questions
Why do I get errors when pivoting Actual and Plan columns?
Errors typically occur if there are multiple identical rows without a valid aggregation method. To fix this, click 'Advanced options' in the Pivot Column dialog box and ensure you choose an aggregation like 'Sum' instead of 'Don't Aggregate'.
Can I use Split Column instead of Pivot Column to separate this data?
No. The Split Column feature is designed to divide text within a single cell based on a delimiter (such as a comma). To change row values into column headers, you must use the Pivot Column feature.
What if I have multiple amount columns for Budget and Actual data?
Power Query's Pivot Column tool only allows you to select one Values Column at a time. To solve this, you first need to unpivot your multiple amount columns into a single column, and then pivot the status column containing your Budget and Actual labels.
