How to add rows to an existing Power BI Table?

by

User Question:

“I manage a dashboard showing HR related data in Power BI and need to add new employee data each month without rebuilding the entire dataset. Is there a way to append rows to an existing Power BI table without starting from scratch? Our team wants to maintain historical data while adding new entries regularly. What’s the most efficient way to add rows to my existing table without disrupting my reports and measures? Can I use a folder connection for this?

Append data to a Power BI table


This comprehensive guide will walk you through two efficient methods to add rows to an existing table: using Power Query to append data and using a folder connection for automatic updates.

We will assume the following data structure: EmployeeTable (EmployeeID: Text, FirstName: Text, LastName: Text, Department: Text, HireDate: Date, Salary: Currency)

Append Data Using Power Query in Power BI

  1. Open your Power BI Desktop file containing the existing employee table.
  2. Click on “Transform data” in the Home tab to open Power Query Editor.
  3. Right-click on your existing employee table and select “Duplicate”.
  4. Rename the duplicated query to “NewEmployees”.
  5. Clear all transformations in the NewEmployees query.
  6. Change the source of NewEmployees to your new data (file or database).
  7. Apply necessary transformations to match the structure of your original table.
  8. Go back to your original employee table query.
  9. Add a custom step by clicking “Add Step” in the Query Settings pane.
  10. Enter the following M code to append the new employees:
= Table.Combine({#"Original Employee Table", NewEmployees})
  1. Click “Close & Apply” to update your data model.

Insert new data using Folder Connection

  1. Create a folder on your computer (e.g., “EmployeeData”) and place your initial CSV file there.
  2. In Power BI Desktop, go to “Get Data” > “More…” > “Folder” and select your EmployeeData folder.
  3. Click “Transform Data” to open the Power Query Editor.
  4. Rename the query to “EmployeeFiles” in the Query Settings pane.
  5. Click on “Combine Files” in the Content column header and choose “Combine & Transform”.
  6. Ensure the column types are correctly detected.
  7. Apply any necessary transformations to clean or format your data.
  8. Click “Close & Apply” to load the data into your model.

To add new employee data in the future:

  1. Create a new CSV file with the same structure as your existing files.
  2. Add the new file to your EmployeeData folder.
  3. Refresh your Power BI report.

Choosing the right data uploading method

Use Method 1 (Power Query Append) when:

  • You have a single, controlled source for new data.
  • You need to perform complex transformations on the new data before combining.

Use Method 2 (Folders) when:

  • You frequently receive new data files with the same structure.
  • You want to automate the process of adding new data.
  • You need to maintain historical data files separately.

Both methods allow you to efficiently add new rows to your existing employee table in Power BI, keeping your HR reports current and accurate. Choose the method that best fits your data management processes and update frequency.

Troubleshooting common issues when updating Power BI tables

  • Column mismatch errors often occur when the new data doesn’t exactly match your existing table structure – resolve this by ensuring all column names and data types are identical before attempting to append the data.
  • Data type conversion failures can happen when appending tables with inconsistent formats – fix this by explicitly setting the correct data types in Power Query before combining the tables, especially for dates and numeric fields.
  • Performance issues with large datasets might arise when appending substantial amounts of data – address this by implementing incremental refresh policies or using direct query mode for very large datasets instead of importing all data.
  • Relationship breaks after appending can occur if your new data contains duplicate keys or missing values in relationship columns – prevent this by validating your new data for referential integrity before appending it to your model.
  • Visualizations not updating after appending new data is a common issue – solve this by checking if your visuals are filtered in ways that exclude new data, or by explicitly refreshing the report after making data model changes.
  • By following these methods, you can efficiently append new data to your Power BI tables without disrupting your existing reports or losing historical information. The approach you choose depends on your specific requirements and how frequently you need to update your data.