How to Add Data to Existing Table in Power BI?

This Power BI tutorial will help how to add data to existing table in Power BI.

Recently, while working on a Power BI report, I encountered a situation where I needed to add data to an existing table. The Append feature in the Power Query Editor of Power BI proved very helpful in this scenario.

In this tutorial, we will learn to append tables in Power BI, apply this process step by step in Power BI, and merge tables using Power BI DAX functions.

Additionally, I will cover how to add a row and column to the existing table in Power BI and add a row to the existing table using Power Query Advanced Editor.

Power BI Append Table

Append Table in Power BI is a function or feature that allows you to merge tables vertically by stacking one table on top of another, thus creating a single table with combined data from both tables.

Example:

Imagine you have sales data stored in separate tables for each region (e.g., North America, Europe, Asia). You can use the append feature to combine these tables into one, making it easier to analyze overall sales performance across all regions.

Append Two Tables in Power BI

This example shows how to append two tables in Power BI.

Follow the below steps to append two tables in Power BI:

1. Open Power BI Desktop. Under the Home tab, click Enter data.

2. After that, you will see a window(Create Table) where you can use the ‘+’ symbols to add new columns and rows. Next, input the data into the table. Once you’ve added the necessary data, give a name to the table (Sales_NorthAmerica), and finally, click on the “Load” button.

3. In the same way, we create another table. Check the screenshot below.

4. Next, under the Home tab, click Transform data.

5. In the Power Query Editor, navigate to the Home tab. Then, expand the Append Queries dropdown menu and select Append Queries.

6. In the Append dialog box, choose the ‘Two tables‘ option, select the tables you want to append and click OK.

7. After clicking the OK button, you’ll see that the two tables have been appended into one table. Click the “Close and Apply” option to save the changes.

This method allows you to load the data into Power BI and then append it to add data to an existing table.

Merge two Tables in Power BI using the DAX function

You know, with the help of the UNION DAX function, we can merge two tables.

Power BI UNION function syntax:

Merged Table = UNION(Table1,Table2,....TableN)

Where:

• Merged Table = This is the resulting table after applying the UNION function.
• UNION Function = Think of the UNION function as a way to stack rows from multiple tables on top of each other.
• Table1, Table2, …, TableN = These are the individual tables you want to combine. Each table should have the same column structure.

Now, we see how to merge two tables in Power BI using the DAX function.

I hope you load two tables with the same column structure as the above example.

1. Goto table view. Under the Table tools, click ‘New table‘.

2. Then, in the formula bar, put the below expression.

Merged Table = UNION('Sales_Europe','Sales_NorthAmerica')

Where:

• ‘Sales_Europe’ = This refers to the table named “Sales_Europe” in our dataset.
• ‘Sales_NorthAmerica’ = This refers to the table named “Sales_NorthAmerica” in our dataset.
• UNION() = Imagine UNION like a tool that helps combine things.
• Merged Table = This is the new table we are creating. It combines the sales data from Europe and North America into one big table.

3. Now, you see the merged table created in the Data Panel. Check the screenshot below.

You can easily merge two tables in Power BI using this UNION DAX function.

Add Column to Existing Table in Power BI

Now, we see how to add a column to the existing table in Power BI.

1. Goto table view. Under the Table tools, click the New column.

2. Then, in the formula bar, put your DAX below the expression.

New Sales = 'Sales_NorthAmerica'[Sales] - 10

Where:

• New Sales = Name of the new Column
• Sales_NorthAmerica = Name of the Table
• Sales = Name of the Column

3. After that, you see one column added in the Table view.

This way, you add a column to the existing table in Power BI.

Power BI Add Row to Table

This example shows how to add a row to the table using enter data in Power BI.

Example:

Imagine you are a sales manager at a retail store and have been tracking your daily sales data in a Power BI report. A new customer enters your store and purchases one day, but you realize the transaction is not recorded in your Power BI report. Add a row to your sales table in Power BI to ensure your sales data is complete and up-to-date.

To do this type of example, follow the below steps:

1. Open Power BI Desktop. Under the Home tab, click Enter data.

2. After that, you will see a window(Create Table) where you can use the + symbols to add new columns and rows. Next, input the data into the table. Once you’ve added the necessary data, give a name to the table(2024 Sales Data). Then click the “Load” button.

3. After loading data. Under the Home tab, click Transform data.

4. In the Power Query Editor, navigate to the APPLIED STEPS section, and under the “Source” step, click on the gear icon.

5. A “Create Table” dialog box will appear once you click the gear icon. From there, you can use the row “+” icon to add rows and input data into each row. After that, click OK.

6. Next, the Power Query editor adds one row to our data set.

Then click Close & Apply and use the data to create the report.

Power BI Add Row to Table using Advanced Editor

In this example, we see how to add a row to the existing table using Power Query Advanced Editor.

I want to add another row to the table above (2024 Sales Data) using the Advanced Editor.

1. Goto Power Query Editor. Under the Home tab, click Advanced Editor.

After clicking the button, the Advanced Editor window will show you the Power Query M code.

2. Now, let’s add the new step using a Power Query M code line. Add a comma after the #”Changed Type” step line, then press the Enter button to create a new line. After that, insert the following M code. After that, change the line after the “in” statement “NewStep.”

NewStep = Table.InsertRows(#"Changed Type",11,{[Date = "10-01-2024",Product = "Wireless Router", Quantity= 1,Price ="$178", Customer = "Matthew Robinson" ]})

Where:

• NewStep = This assigns a name “NewStep” to the new step.
• Table.InsertRows = This function inserts new rows into a table.
• #”Changed Type “ = Refers to the step in the query where the data type was changed.
• 11 = Indicates that we are inserting the new row at the 11th position in the table.
• {[Date = “10-01-2024”, Product = “Wireless Router,” Quantity= 1, Price =”$178″, Customer = “Matthew Robinson” ]} = This is the new row we are adding to the table.

The entire code looks like the screenshot below, but we added highlighted parts.

Then click the Done button.

3. Next, one row is added to our data set in the Power Query editor.

This way, you add a row to the existing table using Power Query Advanced Editor.

Conclusion

With the help of the append query and union function, you can add data to an existing table in Power BI.

This tutorial explored the appended table in Power BI, how to append two tables, and how to merge tables using Power BI DAX functions.

Furthermore, we learned how to add a column to an existing table in Power BI, add a row to a table in Power BI, and add a row to the existing table using Power Query Advanced Editor.

Also, you may like some more Power BI articles:

• Create a Measure Table in Power BI
• How to Center Values in Power BI Table
• Power BI Table Conditional Formatting