Create Dynamic Project Allocation Columns with Power Query M Code in WPS Office
Question details
User needs Power Query M code to calculate project allocation columns dynamically from a table with changing project columns, multiplying each project value by a cost field.
- Product
- WPS Office Spreadsheets with Power Query
- Device & OS
- not provided
- Scenario
- Working in a table that has identifier columns, a cost column, and multiple project columns that may change over time.
- Observed behavior
- Static M code would require manual updates each time project columns are added or removed; the goal is a dynamic query that creates allocation columns automatically.
Make sure the source table is in the current workbook and named Table2, or adjust the table name in the M code. Confirm that the cost column is in the second position and the project columns start after it before pasting the query.
Create Dynamic Project Allocation Columns with Power Query M Code
Use this solution when your source table has identifier and cost columns followed by a changing number of project columns and you want each allocation column calculated automatically.
The M code dynamically reads the column names and field values from the source table. It skips the first two columns, creates an Allocation column name for every remaining project column, and calculates each allocation by multiplying the project value by the cost value.
In WPS Office Spreadsheets, open the workbook containing the source table, click the Data tab, and choose Get Data > From Table/Range or Power Query Editor to open the query editor.
In the Power Query Editor, create a blank query or open Advanced Editor. Paste the following code, replacing Table2 with your actual table name: let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], newFieldNames = List.Transform(List.Skip(Table.ColumnNames(Source),2),(n)=>n & " Allocation"), addAllocation = Table.AddColumn(Source,"Custom",(r)=>Record.FromList(List.Transform(List.Skip(Record.FieldValues(r),2),(q)=>q*Record.FieldValues(r){1}),newFieldNames)), expanded = Table.ExpandRecordColumn(addAllocation,"Custom",newFieldNames) in expanded
Check the new allocation columns in the preview. Click Close & Load to return the results to a new worksheet or table.
Adjust the M Code When Column Positions Are Not Identical
Use this solution if your table has more or fewer leading identifier and cost columns than the example, or if the cost column is not in the second position.
Create Dynamic Project Allocation Columns in WPS Office Power Query
Use WPS Office Spreadsheets and its Power Query editor to paste dynamic M code that calculates allocation columns automatically, even when project columns change.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open WPS Spreadsheets, and load the workbook that contains the source table for project allocations.
- 2. Open the Power Query Editor: Click the Data tab and select Get Data > From Table/Range or Power Query Editor to open the query editor.
- 3. Paste and adjust the dynamic M code: In the Advanced Editor, paste the M code and replace Table2 with your actual table name. Adjust the column skip and cost index only if your table layout differs.
- 4. Load the expanded allocation columns: Review the generated Allocation columns and choose Close & Load to place the results in the worksheet.
- 5. Refresh when source data changes: After adding or removing project columns, click Refresh in WPS Spreadsheets to recalculate the allocation columns automatically.
Frequently Asked Questions
What does the M code skip first two columns for?
The example assumes the first two columns are identifier and cost columns. If your table has a different layout, change the 2 in List.Skip and the 1 in Record.FieldValues(r){1} to match the actual column positions.
Where do I run this M code in WPS Office?
Open WPS Spreadsheets, click Data > Get Data > From Table/Range or Power Query Editor, then open Advanced Editor or a blank query and paste the M code.
Will allocations update automatically when project columns change?
Yes. The query builds allocation column names from the current column names, so after you refresh the connection, any added or removed project columns are picked up dynamically.
Can I use this for a table not named Table2?
Yes. Replace the table name in Excel.CurrentWorkbook(){[Name="Table2"]}[Content] with your actual table name, or create a query from your table first and use that source step.
Is this M code compatible with Microsoft Excel Power Query?
Yes. The M code uses Excel.CurrentWorkbook and standard Table functions, so it works in WPS Power Query and Microsoft Excel Power Query with the same table structure.
