Forum Discussion
Correcting erroneous data
Hello All,
I have a query I'm hoping you can help with. While building a report for a business stakeholder I was informed that roughly 5,000 lines of data in the SQL dataset have some erroneous data. I have been provided the correct data for each of these lines in an excel spreadsheet, is there a best practice for subbing out the erroneous data for the good data? (I know that the right answer is to update the information in the SQL database, however, I want to know if I can correct for it in PBI first before I talk to IT to see if they can update it). In the example below, For the contracts specified I need to pull the early termination date in the excel instead of the SQL. In the dataset I have over 100,000 contracts but only about 5,000 contracts with erroneous data.
SQL Data Example:
| Contract number | Start | End | Early Termination Date |
| 100001 | may 1 2015 | may 1 2020 | April 20 2018 |
| 100002 | may 2 2015 | may 2 2020 | April 20 2018 |
| 100003 | may 3 2015 | may 3 2020 | April 20 2018 |
Excel Corrected Data:
| Contract Number | Early Termination Date |
| 100001 | June 3 2016 |
| 100002 | June 4 2016 |
| 100003 | June 5 2016 |
Hi Anonymous ,
1. You can import both sql data and excel data into power bi desktop
2. In power query, use merge tables to merge these two tables based on [Contract number] column as a new table
3. Remove unnecessary columns, you can retain the excel correct data column or retain both of the early date column to use replace values in power query as your need:
= Table.ReplaceValue(#"Expanded Excel Data",each [Early Termination Date],each [Excel Data.Early Terminnation Date],Replacer.ReplaceValue,{"Early Termination Date"})Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hey Roblu001,
One option would be to go partially through the query editor.
First create a custom column in the excel data called "Correction"
You can left join the Excel file onto the SQL file by the Contract Number
Expand all filed in the new joined SQL data example table
Once you apply changes, you can create a custom dax column in the SQL data table called "Corrected termination date"
What the below column will do is search for the correction fields and if one is a correction it will use the excel date, if it is not a correction it will use the original tables dates.
Corrected Termination Date = IF(Table[Correction] = "Correction", Table[ExcelFile.Early Termination Date], Table[Early Termination Date])By doing it this way you can create a few different measures to quickly analyze the difference the changes might make.
Anonymous , you can load that into power bi and get these get dates populated in table.
You can copy data from one table to another like
City Name = maxx(FILTER(geography,geography[City Id]=Sales[City Id]),geography[City])
Hi Anonymous ,
1. You can import both sql data and excel data into power bi desktop
2. In power query, use merge tables to merge these two tables based on [Contract number] column as a new table
3. Remove unnecessary columns, you can retain the excel correct data column or retain both of the early date column to use replace values in power query as your need:
= Table.ReplaceValue(#"Expanded Excel Data",each [Early Termination Date],each [Excel Data.Early Terminnation Date],Replacer.ReplaceValue,{"Early Termination Date"})Best Regards,
Yingjie LiIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
