How to Add Rows to a Table in Power BI

If you're coming from an Excel background, you might be a bit puzzled when trying to add a new row of data to a table directly in Power BI. You can’t just click on a cell and start typing. That's because Power BI isn't a data entry tool like a spreadsheet, it's a data analysis and visualization tool designed to connect to existing data sources. This post will walk you through the proper, powerful ways to add rows to tables in Power BI, covering everything from adding small bits of manual data to combining entire datasets.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First, Why Can't You Just Type in a New Row?

Before we jump into the methods, it's helpful to understand the "why." Power BI operates on the principle of having a single source of truth. Your data lives in a database, a SharePoint list, an Excel file, or another system. Power BI connects to that source and builds reports based on it. When the source data updates (for example, new sales are added to the database), you simply click "Refresh" in Power BI, and your reports update automatically.

Manually adding rows would break this connection and create data that only exists inside your Power BI file, making it disconnected and difficult to manage. The correct approach is almost always to add rows by combining sources or adding them at the source itself. That said, there are valid scenarios where you need to add rows within Power BI, and that's what we'll focus on.

Common Scenarios for Adding Rows include:

• Combining monthly sales files into a single master sales table.
• Adding a custom sorting table to control the order of categories in a chart.
• Creating a "scaffold" table to fill in missing data points.
• Merging data from different business systems (e.g., website analytics and sales CRM data).

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 1: Manually Adding Data Using "Enter Data"

This is the closest you'll get to typing data directly into Power BI. The Enter Data feature is perfect for creating small, static lookup tables that won't change often. A great use case is creating a table to define a custom sort order for a chart.

For example, let's say you have a bar chart showing sales by business division: "Enterprise," "Mid-Market," and "SMB." By default, Power BI will sort them alphabetically. If you want them to appear in a specific logical order, you can create a simple table to define that order.

Step-by-Step Guide to "Enter Data":

1. Open Enter Data: In the main Power BI Desktop window, go to the Home tab on the ribbon and click on Enter Data.
2. Create Your Table: A "Create Table" window will appear. It looks just like a tiny spreadsheet. You can start typing data directly into the cells. Click the asterisk (*) on a column header to rename it.
3. Name and Load the Table: At the bottom of the window, give your new table a descriptive name, like DivisionSortOrder. Then, click Load.

Your new table will now appear in the Fields pane on the right-hand side. The next steps would be to go to the Model view, create a relationship between this new table and your main data table on the "Division" column, and then use the "Sort by Column" feature to sort your Division field by the Sort Order field.

Limitations: Remember, this method is only for small, static data. It's not efficient for more than a handful of rows and isn't connected to any external source, so it won’t update automatically.

Method 2: Combining Tables by Appending Queries in Power Query

This is the most common and powerful way to add rows in Power BI. The Append Queries feature in the Power Query Editor allows you to stack two or more tables on top of each other, creating a single, consolidated table. This is incredibly useful when your data is split across multiple files or tables but shares the same structure (i.e., the same column headers).

Let's imagine you get a separate sales report for each month: 'January Sales.xlsx,' 'February Sales.xlsx,' and 'March Sales.xlsx.' Instead of analyzing them separately, you want to combine them into one Q1 Sales table.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step-by-Step Guide to Appending Queries:

1. Load Your Data: First, get all your separate tables into Power BI. In our example, you'd connect to and load all three monthly Excel files.
2. Open Power Query Editor: On the Home ribbon, click Transform Data. This opens the Power Query Editor, which is where all data modeling happens. You should see your three sales tables listed in the Queries pane on the left.
3. Choose the Append Option: Select one of the tables you want to append (it doesn't matter which one you start with). Go to the Home tab in the Power Query Editor, and in the "Combine" group, click the dropdown for Append Queries. You have two choices:
4. Select Tables to Append: An "Append" window will pop up.
5. Review and Rename: Power Query will instantly create a new table, likely named Append1, containing all the rows from your source tables. You can click through some of the columns to check that the data combined correctly. Now is a great time to right-click the new query in the Queries pane and rename it to something meaningful, like Q1 Sales.
6. Close & Apply: Finally, click the Close & Apply button in the top-left corner of the Power Query Editor. This will load your newly combined table into the Power BI data model, ready for use in your reports and visuals.

Tips for a Successful Append:

• Column Names Must Match: Appending works column by column based on the column headers. A column named "Order Date" in one table will not combine with a column named "OrderDate" in another, you'll end up with two separate columns. Make sure your column names are identical across all tables before appending.
• Check Data Types: Power Query also considers data types. If a column is a number in one table and text in another, you might get errors. It’s good practice to ensure the data types are consistent before you append.

Method 3: Creating New Rows with DAX Calculated Tables

For more advanced users, you can also add rows by creating new tables using Data Analysis Expressions (DAX). Unlike Power Query actions, which physically shape and transform your data before it's loaded, DAX calculations happen within the data model itself. This method is great for creating tables on the fly for specific analytical purposes.

Using the UNION function

The UNION DAX function does something very similar to appending in Power Query: it combines the rows from two or more tables into a single table.

To use it, go to the Model view or Data view in Power BI. Click on the Modeling tab in the ribbon and select New Table. Then, enter a formula like this:

Combined Sales = UNION('January Sales', 'February Sales')

This creates a new table named Combined Sales that contains all the rows from both the January and February tables. UNION relies on column position, so it's important that the tables you are combining have the same number of columns in the same order.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Using the DATATABLE Function

The DATATABLE function is the DAX equivalent of the "Enter Data" feature. It allows you to define a new table and populate its rows directly within a DAX formula. This is often used to create flexible mapping or parameter tables without having to manually enter the data.

To use it, create a new calculated table and define the structure and content like so:

Region Manager = 
DATATABLE(
    "Region", STRING,
    "Region Manager", STRING,
    "Target Sales", CURRENCY,
    {
        {"North", "Amelia", 150000},
        {"South", "Bruno", 120000},
        {"East", "Camila", 180000},
        {"West", "David", 165000}
    }
)

This formula creates a table with three columns ('Region,' 'Region Manager,' 'Target Sales') and specifies the exact data types. It then populates the table with four rows of data. It's a clean, code-based way to create small tables necessary for your model.

Which Method Should You Choose?

Deciding which method to use comes down to your objective:

• For small, static, lookup, or mapping tables where you need to manually type a few rows, use the Enter Data feature. It's fast, simple, and intuitive.
• When you need to combine multiple data files or tables with the same structure (e.g., monthly reports), Append Queries in Power Query is the best and most robust solution.
• For creating dynamic tables based on existing data or complex logic within your data model, use DAX calculated tables.

Final Thoughts

While Power BI doesn't let you add rows like you would in a spreadsheet, it provides several powerful and structured methods for combining and creating data. For day-to-day work, appending queries in Power Query will be your go-to tool for merging datasets, while Enter Data is perfect for those quick helper tables. Understanding these tools is fundamental to mastering data modeling and building clean, efficient, and scalable reports.

The manual work of appending files, managing data sources, and keeping reports up to date is exactly why we built Graphed. Instead of spending your time in the Power Query editor clicking around to combine CSV files or Excel worksheets, you simply connect your data sources to our platform one time. Then, you can ask questions in plain English like, "show me a dashboard comparing my January and February sales performance," and our AI data analyst builds the combined dashboard for you instantly. We automate the background work so you can skip straight to getting answers.