Add Four Blank Columns in Excel Power Query with M Code
Question details
Create four blank columns dynamically in Power Query using M code instead of adding each column separately through the interface.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- An existing Power Query workflow needs four additional columns named P1, P2, P3, and P4, with null values in every row.
- Observed behavior
- The desired result is a single M step that adds all four columns without requiring a macro or an existing column named P.
Open the query in Power Query Editor and identify the last step under Applied Steps. Check that columns named P1 through P4 do not already exist, because Table.AddColumn cannot add a duplicate column name.
Add four null-filled columns with List.Accumulate
Use one Power Query step to generate P1 through P4 while preserving the existing table and its rows.
List.Accumulate starts with the table from the previous step and runs Table.AddColumn once for each number in {1..4}. The expression each null makes every value in the new columns null; it does not reference an existing column.
In Excel, open Data > Queries & Connections, then double-click the query you want to edit.
In Power Query Editor, select the last item under Query Settings > Applied Steps. If the formula bar is hidden, enable View > Formula Bar.
Click the fx button next to the formula bar to add a new step. Replace its formula with: = List.Accumulate({1..4}, #"Previous Step", (state, current) => Table.AddColumn(state, "P" & Number.ToText(current), each null)). Replace #"Previous Step" with the actual preceding step reference, such as #"Changed Type". If your input table is already defined as table1, you can use table1 instead.
Press Enter and scroll to the right in the preview. Confirm that P1, P2, P3, and P4 contain null values, then select Home > Close & Load to apply the query.
Work with your exported query results in WPS Spreadsheet
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility for an easy transition. Use WPS Spreadsheet to edit the loaded results saved in an Excel workbook; it is not a substitute for executing or refreshing this Power Query M step.
Frequently Asked Questions
Does the code require an existing column named P?
No. In Table.AddColumn(state, "P" & Number.ToText(current), each null), "P" is only the column-name prefix. The expression each [P] would copy values from an existing P column and would fail if that column were absent.
How do I add more than four blank columns?
In the Power Query formula bar, change {1..4} to the required range. For example, {1..10} creates P1 through P10 using the same List.Accumulate expression.
Why does Power Query report that the previous step was not recognized?
The reference #"Previous Step" is a placeholder. Select the preceding step under Applied Steps and use its exact name in the formula, such as #"Changed Type". A separately defined query or table named table1 can also serve as the starting table.
What happens if P1 already exists in the table?
Table.AddColumn returns an error when a generated name already exists. Before adding the new step, rename the existing column by double-clicking its header, or change the "P" prefix in the M expression to an unused prefix.
Can I specify a data type for the new blank columns?
Yes. Add the optional fourth argument to Table.AddColumn. For nullable text columns, use: Table.AddColumn(state, "P" & Number.ToText(current), each null, type nullable text). You can make this change directly in the formula bar.
