Forum Discussion
Correcting specific data values from an imported corrections table
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...
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.
2 Replies
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.
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
