Match a phone number to a list of country codes

[Nick]

I am trying to match a phone number to a list of country codes in excel to know the country.

The problem is that I want to match the first x characters of the phone number to the country code (where x is the length of the country code in the list) as not all the country codes have the same length (eg. US:1 UK: 44 Jordan:962, UAE:971, Bahrain:973).

I was able to do it in Excel via the following formula, but now struggling to convert it to Power BI/Query:

=LOOKUP(0,-SEARCH(LEFT(A1,LEN(CountryCodes!$D$1:$D$6))+0,CountryCodes!$D$1:$D$6),CountryCodes!$E$1:$E$6)

where CountryCodes!D1:D6 - Country Code
CountryCodes!E1:E6 - Country

Is this feasible?

Thanks in advance,

Nick

[grprem81us]

This does not work in Power BI Query.

Can anyone help to convert this to Power Query

=LOOKUP(0,-SEARCH(LEFT(A1,LEN(CountryCodes!$D$1:$D$6))+0,CountryCodes!$D$1:$D$6),CountryCodes!$E$1:$E$6)

Thanks in Advance.....

[BhaveshPatel]

SAMPLE DATA PLEASE SIR

[Nick]

Is this enough?

[BhaveshPatel]

THIS WOULD BE OF GREAT HELP.

https://www.powerquery.training/portfolio/replicate-excels-vlookup-function/

[Nick]

Thanks.

I tried already yesterday but could not get it work so far since VLOOKUP approwimative match doesn't help really.

Here is a false result for anumber from North America:

Regards,

Nick

[BhaveshPatel · Accepted solution]

Hi Nick

Below is the the link to the country codes.

Create a conditional column in the powerquery based on the below list.

https://drive.google.com/file/d/0B5-C_3XrFPdOZXkwNkZXb3pGdGs/view?usp=sharing

This would solve your problem.

This is a really lenghty solution but it definitely works.

Regards,

Bhavesh

[piun]

can you send the link ?

[Nick]

Great thanks! Had to fix some syntax error (lowercase and speeling syntax) but all good. Thanks again, Nick