Forum Discussion
Enter a data validation in a Power BI or Power Query query
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.
Community Championcan you give more details as in what exactly you would like to validate and what action to take if it isn't?
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
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.
Hello Vanessafvg,
Thanks you very much for your explanation.
Best regards.