Graphed LogoGraphed
Login

How to Add a Row in Power BI Transform Data

Cody Schneider•

Need to add a new row of data directly within Power BI’s Transform Data window? It’s a common data prep task, whether you need to insert a default record, a placeholder, or a manual summary line. This tutorial will walk you through a few practical methods to add a row to your table in the Power Query Editor, from the easy-to-use interface to more advanced M code techniques.

Why Would You Need to Add a Row in Power Query?

While Power BI is designed for visualizing existing data, there are times during the data transformation process (the "T" in ETL) when inserting a row is the best solution. Instead of going back to the original source file like an Excel sheet, you can handle it directly in the Power Query Editor.

Common scenarios include:

  • Adding a Default or 'All' Option: If you're building a report with a slicer, you might want to add a row that represents an 'Overall' or 'Company-Wide' view that doesn't exist in your source data.

  • Inserting Manual Adjustments: Sometimes you need to add a single transaction or record that was missed in the original data export without wanting to regenerate the entire source file.

  • Creating Placeholder Rows: You might insert a blank or template row to structure your table for later steps or to ensure certain categories are always present, even if they have no data yet.

  • Fixing Gaps in a Sequence: If you have data with missing dates or sequential IDs, you might manually insert a row to bridge a specific gap discovered during your analysis.

Though creating dynamic summary rows (like totals) is often better left to DAX measures in the report view, adding static, predefined rows is perfectly suited for Power Query.

Method 1: The 'Enter Data' & Append Technique (The Easiest Way)

The simplest and most visual method for adding a row is to create a new, tiny table by hand using the "Enter Data" feature and then merge it with your main table using "Append Queries." This approach requires no coding and is ideal for beginners.

Step-by-Step Guide:

1. Go to the Home Ribbon and Click 'Enter Data'

Inside the Power Query Editor, make sure you are on the Home tab. Look for the Enter Data button. Clicking this will open a small window where you can build a new table from scratch.

2. Create Your New Row

In the "Create Table" window, you'll see a small grid. This is where you'll define the single row you want to add.It is essential that your column headers here perfectly match the column headers in your main table where you want to add the row. PBI is case-sensitive, so "Sales" is different from "sales".

  • Double-click "Column1" to rename it to match your first target column (e.g., "Country").

  • Click the asterisk (*) icon next to the column name to add a new column and rename it (e.g., "Product Category").

  • Repeat this for all columns in your main table that you want to populate.

  • In the first row of the grid, type in the values for your new record.

For example, if you want to add a placeholder row for a new sales region, it might look like this:

Give your new table a memorable name at the bottom (like "NewBranchRowToAdd") and click OK. You will now see this new one-row table in your Queries pane on the left.

3. Set the Correct Data Types

Power Query often defaults new data to the "Any" (ABC/123) data type. Click on your new one-row query and go through each column, setting the correct data type (e.g., Text, Whole Number, Date) to match the corresponding columns in your main data table. This prevents errors during the next step.

4. Append the New Row to Your Main Table

Now it's time to join them. Select your main data query (the one you want to add the row to). On the Home ribbon, click the drop-down arrow next to Append Queries and select "Append Queries".In the Append window, select the table you just created ("NewBranchRowToAdd" in our example) from the "Table to append" dropdown list. Click OK.Your new row will now appear at the bottom of your main table!

Method 2: Using a Blank Query & M Code (The Flexible Way)

This method accomplishes the same goal as the first one but gives you more control by using a bit of Power Query's M language. It's great if your row has many columns, as typing code can be faster than clicking to create each column in the 'Enter Data' interface. Don't worry, the code is very simple!

Step-by-Step Guide:

1. Create a New Blank Query

In the Power Query Editor, go to the Home tab, click the New Source dropdown, and select Blank Query.

2. Open the Advanced Editor

With your new blank query selected (it will probably be named "Query1"), click on Advanced Editor from the Home tab.

3. Write the Code to Define Your Row

Erase the default text in the Advanced Editor and enter M code to create a single-row table. The syntax uses Table.FromRecords({...}). Inside the curly braces {}, you define your row within square brackets [].

Simply list each column name followed by an equals sign and the desired value, separated by commas. Remember to match column names and data types exactly.

Example M code:

Let's say your main table has columns named "Product ID", "Salesperson", "Units Sold", and "Sale Date".

Code breakdown:

  • Table.FromRecords({...}) is the M function that builds a table.

  • The outer curly braces {...} create a list. In this case, it's a list containing one item: our new row.

  • The inner square brackets [...] define a single record (our row).

  • #"Column Name" = "Value" is used for column names that contain spaces. Regular names don't need the quotes and hash.

  • #date(YYYY, MM, DD) is how you properly format a date in M code.

4. Click 'Done' and Append the Query

After pasting your code, click Done. You now have another one-row table query. Just like in Method 1, you can now select your main query and use the Append Queries function to combine them.

Method 3: Insert a Row at a Specific Position (The Advanced Way)

Both previous methods add the new row to the very end of your table. But what if you need to add it to the top, or somewhere in the middle? For that, you need to use the Table.InsertRows M function.

This powerful function lets you insert one or more rows at a precise index number within your table. This method requires working directly in the formula bar or the advanced editor of your main query.

Step-by-Step Guide:

1. Identify the Previous Step

In your main query, look at the APPLIED STEPS panel on the right. You need to insert the row after a certain step. A good choice is usually the last step, such as "#Changed Type," or any step where your table is in its final structure.

2. Create a New Custom Step

With the last step highlighted, click the small "fx" icon in the formula bar to create a new custom step. This new step will automatically reference the prior step's output.

3. Write the Table.InsertRows Code

In the formula bar, replace the default code with the Table.InsertRows function. The syntax is:

  • table to modify: This will be the name of the previous step, like #"Changed Type".

  • offset number: The position where the new row should be inserted. 0 is the very first row, 1 is the second, and so on.

  • rows as a list: This is the same record/row definition we used in Method 2, wrapped in {} to form a list.

Example code to add a row at the top:

Once you press Enter, Power BI will insert this new row right at the beginning of your table. After you're done, remember to give your new step a descriptive name (e.g., "Inserted Header Row") by right-clicking it in the APPLIED STEPS pane.

Final Thoughts

Mastering how to add rows within Power Query is a valuable skill for any Power BI user, helping you clean and shape your data before it even reaches the visualization stage. Whether you prefer the simple UI-driven approach of 'Enter Data' and 'Append' or need the precision of the Table.InsertRows function, you now have the tools to handle common data gaps and adjustments efficiently.

Manual data preparation like this is a necessary part of most reporting workflows, but it's often the most time-consuming. At Graphed, we've built a platform that simplifies this entire process. Instead of getting bogged down in Power Query steps or learning M code, you can connect your data sources in seconds and simply describe the reports you need in plain English. Our AI-powered analytics engine handles the transformation and visualization, turning hours of setup into a 30-second conversation and letting you focus on answering business questions, not wrestling with tools.