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. 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.

Upload 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 loading 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

  • Mismatched columns: Ensure all columns in the new data match the original table in name and data type.
  • Duplicate entries: Add a step to remove duplicates based on EmployeeID if necessary.
  • Performance issues: For large datasets, consider incremental refresh or DirectQuery mode.
  • Refresh errors: Check data source connections and permissions.
  • Inconsistent file structures: When using folder connection, ensure all CSV files have identical column names and types.
  • File type mismatch: Make sure all files in the folder are of the same type (e.g., all CSV).