Enter a data validation in a Power BI or Power Query query

[Anonymous]

I would like to know if it is possible to perform a validation of data of a column in a query to verify if the data is correct and to change the incorrect data by the correct data.

Thank you very much and greetings.

[vanessafvg]

can you give more details as in what exactly you would like to validate and what action to take if it isn't?

[Anonymous]

Hello Vanessafvg,

I have a query like the following:

I want to verify that each of the values that are in that column are in a list of unique values, and if some of those values are not in the list of unique values, those values are replaced by the closest value.

For example, if 46500000 is not a correct value, all 46500000 in that column are replaced by 462000000.

Thank you very much and greetings. 

[vanessafvg · Accepted solution]

my suggestion is that you do a merge in power query  with your list that you want to validate against the list that has the valid numbers using an anti join to pull back all the non matching values which dont match.

more explanation here.

http://radacad.com/how-to-change-joining-types-in-power-bi-and-power-query

in terms of your list for correcting the numbers, how many correct values do you have, this part is probably going to be more complicated depending on how much data you have, i guess you would need to find where the smallest difference is between  the invalid number and a valid number but how to do that exactly , not sure maybe someone else can assist there.

[Anonymous]

Hello Vanessafvg,

Thanks you very much for your explanation.

Best regards.