Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 d...
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    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 Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

westwrightj's avatar
westwrightj
Icon for Resolver III rankResolver III
6 years ago

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.