Correcting specific data values from an imported corrections table

[Anonymous]

Hey,

I have a dataset which has errors in it, I'm unable to get them corrected by the owner of the data so I want to correct the errors each time it is loaded from the source by taking values from a correction table and leave the rest from the source unchanged.

I've tried merges, apending rows (but causes other issues when the source changes), etc.

Here is a simplified example of the source:

Inventory Number	Product Type	Motor Type	Fuel Type
INV-000123	Car	Combustion	Petrol
INV-000456	Boat	Electric	Electricity
INV-000789	Cycle	Manual	Petrol
INV-000234	Trailer	Towed	None

Correction table with the values that need correcting:

Inventory Number	Product Type	Motor Type	Fuel Type
INV-000789			None
INV-000234			N/A

End result:

Inventory Number	Product Type	Motor Type	Fuel Type
INV-000123	Car	Combustion	Petrol
INV-000456	Boat	Electric	Electricity
INV-000789	Cycle	Manual	Petrol
INV-000234	Trailer	Towed	N/A

The data set is much larger than this but hopefully this is enough to demonstrate what I am trying to do...

[Anonymous · Accepted solution]

Hi, @Anonymous 

@Nithinr has provided a scheme to you. I have the following additions. Based on your description, I use the following sample data:

sheet1 table:

sheet2 table:

The relationships between the tables are as follows:

I did this by using the following DAX expression in measure:

correct type measure =
VAR _id =
    CALCULATE ( MAX ( Sheet1[Inventory Number] ) )
VAR _type =
    CALCULATE ( MAX ( 'Sheet1'[Fuel Type] ) )
VAR _id1 =
    CALCULATE ( MAX ( Sheet2[Inventory Number] ) )
VAR _type1 =
    CALCULATE ( MAX ( 'Sheet2'[Fuel Type] ) )
RETURN
    SWITCH ( TRUE (), _id = _id1, _type1, _id <> _id1, _type )

Use calculate to obtain the Inventory Number and Fuel Type of the current row. Use the switch to determine whether the Inventory Number exists in the correct table, if it exists, the Fuel Type is the value in the correct table, and if it does not exist, it is the value of the current table.

Here are the results:

If you want to do this with calculated columns, here's a workaround:

Column =
VAR _id = 'Sheet1'[Inventory Number]
VAR _type = 'Sheet1'[Fuel Type]
RETURN
    SWITCH (
        TRUE (),
        _id = RELATED ( Sheet2[Inventory Number] ), RELATED ( Sheet2[Fuel Type] ),
        _id <> RELATED ( Sheet2[Inventory Number] ), 'Sheet1'[Fuel Type]
    )

The DAX execution result of the calculated column is as follows:

I've provided the PBIX file for this time below, and it would be great if I could help you

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[Nithinr]

Create a relationship between your source and correction table

Use the calculated column 

Corrected = IF(RELATED(correction[Inventory Number]) == BLANK(),original[Fuel Type],RELATED(correction[Fuel Type]))

Corrected = IF(RELATED(correction[Inventory Number]) == BLANK() || RELATED(correction[Fuel Type]) == "None" ,original[Fuel Type],RELATED(correction[Fuel Type]))

use this for fuel type, you can adjust formula as per your requirement, use any one of them from above