Error message : Expression.Error

[Mederic]

Hello everyone,
I am trying to reproduce the exercise in this link, but the query returns an error message.

https://we.tl/t-RdkGCHe1LK 
Can you help me correct the error?
Thank you in advance.

Best regards

[ronrsnfld]

This line is incorrect:

Source = RunDataQualityCheck(MyTestTable, "MyTestTable", DataQualityActionTable)

DataQualityActionTable is the name of the variable. You need to replace that with the name of the appropriate table.

[Mederic]

Hello @ronrsnfld ,

Thank you for looking into the issue.
I applied the same arguments as in the screenshot below, but it is not working.
I do not see what I need to change in the function.

Best regards,

[ronrsnfld]

Examining the lesson more closely, it seems there are multiple errors.

The cause of your specific error is in the _ValidateAge function.  You copied it verbatim from the lesson as 

(data as nullable text) as logical =>
    let
        isValid = if [YourValidationRuleHere] then true else false
    in
        isValid

Contrary to the instructions, you did not replace [YourValidationRule] with a functioning validation rule, hence the error which propagates to your result.

Please note that the lesson suggests with regard to the Validation Functions: "You can easily debug your M code by adding a custom column to your original table." Had you done that, you would have found the error when you checked that validation rule.

You should do that anyway to ensure the rules function as you wish.

On the good side, it seems that the RunDataQualityCheck code does seem to work properly, so once you get the actual rules sorted, you should be OK.

Good learning experience!

[Mederic]

Thank you @ronrsnfld ,
Indeed, I had overlooked that part, and thank you for your feedback.
However, I find it unfortunate that to obtain the final result, we still need to add a column to the initial table.

And why does this step not simply appear in the statement !!

I will try to understand all this.
Thank you.
Best regards.

[ronrsnfld]

I don't understand what you mean?

The final result has only the three columns -- phone number | email | age cleaned as per the validation rules.

The added columns are only a suggestion for troubleshooting the validation code. Once you have satisfactory code, those steps are not required.

Here is what the final output might look like, although the validation code for email is clearly incorrect and the data types have not been set.

[Mederic]

Sorry, my English isn't very good, so I sometimes have trouble understanding or explaining myself.
How do you get the final result without any mistakes ?
Could you please post the corrected file ?
This exercise is quite complex and I'm having trouble understanding it 😊.

Thanks in advance

Best regards

[ronrsnfld · Accepted solution]

The result I got without the error message I obtained by editing the _ValidateAge function. I pointed out the problem with what you sent me in a previous message. However, the result is not correct as none of the emails were properly validated. You will have to correct that.

The _ValidateAge method that I used is was to just check the ages to ensure that they are a integer.

(data as nullable text) as logical =>
    let
        isValid = (try Int64.From(data))[HasError]=false
    in
        isValid

But you are going to have to edit your _ValidateEmail function as it is returning an incorrect result

[Mederic]

Hello @ronrsnfld ,

Thank you for correcting the code for age.
I finally understood what was expected for each of the three criteria.
For the email verification, I asked Claude.AI, who suggested the code below.
I now get the same result as in the statement (file).
But it's a strange lesson when I realise that everything wasn't correct or complete.

Thank you.
Best regards

(email as nullable text) as logical =>
    let
        isValid = 
            if email <> null and
               Text.Contains(email, "@") and
               Text.Contains(email, ".") and
               Text.Length(email) - Text.Length(Text.Replace(email, "@", "")) = 1 and
               Text.PositionOf(email, "@") > 0 and
               Text.PositionOf(email, ".", Occurrence.Last) > Text.PositionOf(email, "@") and
               Text.PositionOf(email, ".", Occurrence.Last) < Text.Length(email) - 2 and
               not Text.StartsWith(email, "@") and
               not Text.EndsWith(email, ".") and
               not Text.Contains(email, "..") and
               not Text.Contains(email, "@.")
            then true 
            else false
    in
        isValid

[ronrsnfld]

I was incorrect. The problem is deeper into the function. I will look at it more closely.