Contenidos
One of the characteristics of Power BI (and Power Pivot) is that the data cannot be changed once it is loaded into the database, this is by design. Power BI is a reporting database, not a spreadsheet. Power Pivot and Power BI are designed to accurately report on the data being uploaded and do not allow a user to "change" the data after loading. This paradigm is very different for those who live in an Excel world. When you use Excel, you can override any number (or numbers) you want, in case you need to make such a change.
The best solution: fix the font!
Let me make this statement in advance and be clear. The best way to troubleshoot your source data is to go back to the source and correct the problems there. This is my recommendation on how you should solve these problems. However, sometimes that is not possible for some reason. This article will explain how you can use Power Query to override bad data during loading when you can't change it in the source, for whatever reason.
Self-reference tables
One option you could use to change the data is to use the concept of self-referencing tables that I wrote about in this article. This approach can work, but it has a downside. You should load all the data from the source into Excel and only change the individual data that is incorrect. While this may work for small data sets, it is not practical for larger data sets.
Use override tables
A better way to solve this problem with Power Query is to create an Excel table that contains the exceptions that need to be fixed. Then load that Excel table into Power Query along with the source and replace the bad data with the corrected data. I wrote an article on how to substitute individual data points in 2017. In today's article, I'll show you how to use substitution tables to replace entire records in your source data.
I am using the Adventure Works database to demonstrate.
Replacing bad records with an override table
Here are the steps to replace bad records with good records using an override table.
- Identify bad records in the source table.
- Take a copy of the entire records and add it to an override (exception) table in Excel.
- Make any necessary changes to the registries.
- Use Power Query to remove the original records from the source and replace them with the modified version.
Here is a step by step demonstration
The following entries are for an invoice (# 1) in the Sales table. Let's say the cost (# 2) of an order line is wrong.
You must identify the records (rows) to be corrected. You can do this from the data view in Power BI Desktop. Just go to the data view, filter by the invoice number, and then copy the table that contains all the rows for that invoice.
- You can then paste the records into an Excel spreadsheet.
- Convert the records to a table (or append to an existing table if you already have one). Give the table a good name, say InvOverrides.
- See a sample below.
- Keep only the rows that you want to modify and delete the rest. You can do this in Power BI when you first extract the records, or just do it in Excel once you have a copy uploaded there.
- Make the necessary corrections in each row (see below in green).
- In Power Query within Power BI, create a new query and load the InvOverRides table from the Excel file.
- Create a staging query for the Sales table and name it SalesStaging. You can do this by right-clicking on the final step of the current sales table and selecting "Extract Previous".
- You now have the SalesStaging query with the original data rows.
-
To remove the rows that need to be corrected from the original table, simply merge the SalesStaging query with InvOverRides using a anti union left. This will keep the records that only exist in the source table and remove the ones that also exist in the InvOverRides table, exactly what you need. Please note well: I have joined the 2 tables as shown below using 2 columns for the join. This is necessary to correctly identify unique rows (2-column concatenation makes each row unique). This may be different with your data.
The dependency view of the final query will look like this.
Wrap
There are variations on how you can complete this exercise. You don't technically need the setup table, but I like to create one to keep the handling of changes away from the final table to be loaded.
