I was looking for a simple way in Power BI Desktop and Power Query to manually add a row to an existing table using Power Query M. This is not supported through the standard Power Query interface, but is possible by using the Advanced Editor.
In this example, I add an extra row to the Ticket priority dimension table to represent unknown or null values that occur in the Ticket fact table.
This is what the Ticket priority table looks like before making any changes.
To handle missing values, I add a new row with a priority_id of 0 and a priority value of Unknown.
Advanced Editor
To do this, click Advanced Editor in the menu. You can find it on both the Home tab and the View tab.
After clicking the button, the Advanced Editor opens and displays the underlying Power Query M code. If you’re new to this, I won’t go into a detailed explanation of how Power Query M works—there are plenty of good resources available for that. Instead, I’ll focus on explaining how the lines of code you see relate to the applied steps you may already be familiar with.
In the code shown, each line corresponds to an applied step displayed in the Power Query editor on the right-hand side.
In Power Query M, and in this example, everything between the let and in statements defines the sequence of steps. Each line starts with the step name, followed by an equals sign, and then a Power Query M expression. Every step ends with a comma and produces a table as its output.
The line after the in statement specifies which step provides the final result. In this case, the output comes from the last step, #"Changed Type".
Add a step
Now let’s add a new step using a line of Power Query M code. Start by adding a comma after the #"Changed Type" step and press Enter to create a new line. Then copy and paste the following line:
#"Add Unknown row" = Table.InsertRows(#"Changed Type", 0, {[priority_id=0, Priority="Unknown"]})
With this line, we create a new step called Add Unknown row and use the Table.InsertRows function to insert a new row into the table. For details on how Table.InsertRows works, refer to the Microsoft documentation.
The syntax for this expression is as follows:
Table.InsertRows(table as table, offset as number, rows as list) as table
The Table.InsertRows function takes three parameters: a table, an offset, and a list of rows. In this case, the table is the output of the previous step, #"Changed Type", and the offset is 0, which places the new row at the top of the table.
The row itself is defined between the brackets. Here, we set the priority_id column to 0 and the Priority column to **”Unknown”`. If your table has additional columns, simply add them within the brackets, separated by commas, and assign each column its corresponding value as shown in the example.
Finally, update the line after the in statement to #"Add Unknown row" so that the query returns the output of the newly added step.
After clicking Done, you’ll see the new step added under Applied Steps, and the table will show the newly added Unknown row as the first row.
