It’s best practice to modify data before it comes into Power BI but it’s not always practical. We’ll highlight a few ways you can edit data directly in Power BI to help you present the results that you need on your dashboard and also dive into some best practices for managing Power BI data sources to avoid having to edit data imported into Power BI when possible.
Power BI doesn’t support a quick way to double click on a cell and edit incoming data like you can in an Excel spreadsheet, we’ll explain why and some workarounds.
Let’s jump in!
Understanding Power BI Data Integrity
Power BI’s functionality is rooted in its ability to provide real-time insights through live data streams and automatic refreshes. This design makes manual data adjustments within Power BI impractical. It is a best practice to address errors as close to the data source as possible. It makes your numbers easier to re-create and keeps everybody on the same page. If other members of your organization are unaware of manual adjustment it can cause confusion and cast doubt on your report if they attempt to re-create it in the future.
There are several advantages to updating information in a source system:
- Auditability – Direct adjustments in the source systems creates an easy-to-follow audit trail.
- Consistency in Reporting – Ensures that all reports based on the same data reflect the same results.
- Refresh Optimization – Power BI is designed to support live data streams and automatic refreshes. If data has to be constantly manually adjusted you will not be able to deploy these powerful features.
Because of the number of issues that are associated with manual adjustments, we recommend starting with data that is as close to the source system as possible, ideally connecting Power BI directly to it. However, we also recognize that it is not always possible to do so.
Manually Adjusting Data in Power BI
Because Power BI is designed to stream incoming data, it’s not always the easiest to manually adjust it like you would be able to using Excel. However, here are some ways you can do it when you’re in a pinch or just need to make a small quick adjustment without going through the entire process of working with another department or trying to backdate adjusting entries.
Modify the Source Excel or CSV Document
If you’re working with a .csv or .xlsx file, the easiest way to make changes is to open the file, edit it in Excel and save it. When you go back to Power BI the information won’t automatically update. Make sure that you’ve closed out of the source file then navigate to Home on the Power BI Ribbon, and click Refresh.
Every time you click the refresh button, Power BI will connect to the incoming data sources in your report and pull in the new information. If you make a change in Excel and the change doesn’t show up right away, try to refresh the report to bring in updated data.
Find and Replace Values
For fast updates, launch the Power Query editor and use it to find and replace values that need to be updated. To launch the Power Query Editor in Power BI, right click on a table and select Edit Query. Most of the data adjustments you will do in Power BI, including replacing values and other transformations take place using Power Query.
Once launched, you can right click on any of the column headers on the top of the Power Query Editor and select replace values. If the value you need to update is unique, then this is very simple and quick.
You do have to be careful that the incoming data is unique because Power BI will replace all instances of that number when it occurs in the column. Even if it doesn’t exist in the starting dataset, be careful if you plan to update the incoming data at some point in the future the number could show up and be replaced causing your final numbers to be off.
When doing this it’s also easy to forget that you made an adjustment several months or years ago, even though the replace will show up as an applied step in Power Query. You can always double click on the step and re-name it to give yourself a future clue.
Conditionally Replace Values based on Row Number
Another way to replace values in Power BI is to use an index column. This method can be handy when you have to replace a very specific number and the value occurs multiple times in a dataset. Instead of right clicking and replacing a number with a new one, you have to create a conditional column that uses logic such as: IF Index Column = Row 200 then “New Number” else leave it alone.
Here’s a quick overview of how to do this using the Power Query Editor.
First, create an Index column, this becomes the reference later on. Go to Add Column, Index Column.
Then go to Add Column, Conditional Column. The setup will look similar to the image below. Where you are creating a brand new column that says if the Row Number = wherever your number is then replace it with a new number. Else use the original starting column value.
This method creates a new column, but you can remove the old one, and rename the new one to match what the old one ways. Power BI reports are very dependent on column names and Power Query executes before the report and dashboard designer side of it. As long as your column names are the same when you close and refresh Power Query the rest of your report won’t know the difference.
Append an Adjustment File or Table to a Dataset
This is our preferred method because it gives you an audit trail, and makes it easy to make future adjustments. The basic idea is to use a separate Excel file as an adjustment file that keeps track of your data changes. This method works because most of Power BI gets presented in a dashboard as a SUM or other aggregated value.
When Power BI calculates the starting data with the adjustment data it will net the two together and show the end user the numbers you want them to see.
Example of starting data in Power BI:
Example of adjustment data in an Excel file.
To use this method, Get Data in Power BI and import the adjustment file. Make sure that the column headers match up with the data already in Power BI and then go the Power Query Editor, Home, and Append Queries. Appending will combine both of the tables so you will have one long dataset with original data and the adjustment rows.
It’s also possible to manually create a new data table in Power BI that can act as an adjustment table if you don’t want to manage a separate Excel or csv file. Power BI data tables are saved directly into the .pbix file for even faster editing and adjustments.
Avoiding the Need for Manual Adjustments
The best way to manually adjust data in Power BI is to avoid it completely. We acknowledge that it’s not always possible or worth the amount of time and effort that it can take but fixing it the right way usually ends up being a time saver in the long run.
If you find that you have data cleanliness issues or your accounting team is providing you with bad numbers, consider creating additional check tabs on your Power BI report that lets you make sure that values tie out. Or, if you’re having trouble creating a data model that ties out to a report that managers are used to looking at, consider using that as a basis for Power BI. It’s not a best practice, but it does let you fake it until you make it.
Another strategy is to connect directly to a source system and highlight when there are errors in your reporting. When you make it clear that there are data entry errors, you may not be an office favorite, but it does show management that they have some issues that they need to address with their existing setup. Many errors occur due to bad data entry, lack of staffing, or lack of checks and balances to ensure that incoming data is accurate.
Correcting bad data at the source is the best way to ensure that changes between source data and Power BI transformations are unnecessary. The more manual changes you make the more difficult it is to audit, and re-create the numbers being presented. However, it is not always practical or possible.
When manually adjusting data coming into Power BI, the fastest easiest way is to update a value in an incoming Excel or CSV file. Open the file, change the values, save it and refresh your Power BI report. When that’s not possible, consider finding and replacing values when you can accurately define which row or value needs to be updated.
For large scale changes that need an audit trail, create a separate data source that can be appended to incoming data. When Power BI aggregates the values they will net together though there can be some issues depending on the aggregation method so it’s best to double check the results manually before going live.