Can I extend out the criteria of a conditional column in Power Query

[Anonymous]

I have created a bunch of rules in a conditional column that identifies the area code of New Zealand telephone numbers including any mistakes done during data entry. For example, we identified a pattern where a few different versions of a phone number might exist such as:

1. +64 27 505 5050
2. +64 27 0275055050 (user mistake)
3. 027 505 5050
4. (027) 5055050

The first thing I did was removed spaces and special characters. So each number as text looks like 64275055050

With my conditional columns, I am going through an exrcise of cleaning up country_code, area_code and dial _number. For identifying the area code 27 (for example) I have these criteria:

1. IF phone_number begins with 6427027 then set are_code to 27
2. ELSE IF phone_number begins with 6427 then set are_code to 27
3. ELSE IF phone_number begins with 64027 then set are_code to 27
4. ELSE IF phone_number begins with 027 then set are_code to 27

There are a finite number of national phone numbers for landland, mobile and toll free so, it's not a huge task.

The problem I'm trying to resolve

So the logic works really well but then, there are some dial_numbers though that have ended up being 4 numbers long when the minimum length os 7. I worked out why, there are actually some phone numbers where 6427027 is the beginning of the phone number and not a mistake.

So, in order for me to accommodate this, I need an AND clause in my condition for those ones to include IF length of phone_number minus 7 is more than equal to 7 then this second part would not be true for valid numbers like 6427027.

Since I used a conditional column, there's no way of adding in a formula as a criteria in the wizard, I wondered whether I can use the advanced editor to do this.

The line I want to modify is

`if Text.StartsWith([phone_number], "6421021")` AND above logic `then 7 else` - can someone please let me know how I might write this? Thanks, Matt

    #"Added Conditional Column2" = Table.AddColumn(#"Changed Type1", "dq_phone_trim", each if Text.StartsWith([phone_number], "6421021") then 7 else if Text.StartsWith([phone_number], "6421") then 4 else if Text.StartsWith([phone_number], "64909") then 5 else if Text.StartsWith([phone_number], "6427027") then 7 else if Text.StartsWith([phone_number], "6427") then 4 else if Text.StartsWith([phone_number], "64202") then 5 else if Text.StartsWith([phone_number], "6422022") then 7 else if Text.StartsWith([phone_number], "022") then 3 else if Text.StartsWith([phone_number], "09") then 2 else if Text.StartsWith([phone_number], "649") then 3 else if Text.StartsWith([phone_number], "64606") then 5 else if Text.StartsWith([phone_number], "646") then 3 else if Text.StartsWith([phone_number], "6422022") then 7 else if Text.StartsWith([phone_number], "6422") then 4 else if Text.StartsWith([phone_number], "643") then 3 else if Text.StartsWith([phone_number], "647") then 3 else if Text.StartsWith([phone_number], "021") then 3 else if Text.StartsWith([phone_number], "6428028") then 7 else if Text.StartsWith([phone_number], "644") then 3 else if Text.StartsWith([phone_number], "6428") then 4 else if Text.StartsWith([phone_number], "64800") then 5 else if Text.StartsWith([phone_number], "027") then 3 else if Text.StartsWith([phone_number], "6420") then 4 else if Text.StartsWith([phone_number], "6429") then 4 else if Text.StartsWith([phone_number], "6403") then 4 else if Text.StartsWith([phone_number], "64021") then 5 else 0),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column2", "dial_number", each Text.End([phone_number],Text.Length([phone_number])-[dq_phone_trim])),

[MFelix]

Hi @Anonymous ,

Not really sure if I understand the request if the number starts by 6421021 you want to return what value?

Or do you want to first return the length of the number and then check ?

[Anonymous]

Apologies to you, I didn't get a notification on this and went on holiday 😞 

The answer to your question:

Or do you want to first return the length of the number and then check ? Yes - I want to only di the condition using 6421021 if the length of the number is greater than 11 characters. If less than, I would use 6421. In both cases, the value that I am resolving is 21 (area code) and the length of trim of 6421021 is 7 characters whereas 6421 is 4 characters. I then trim the dial number to the length of the number minus trim value (4 or 7).

So, in Power Query Conditional column, in the wizard, I can only add one ciritera per condition, I am hoping I can modify in advanced editor to add in the length criteria.

Hope that makes sense