Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Nick
Frequent Visitor

Match a phone number to a list of country codes

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

1 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

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

8 REPLIES 8
grprem81us
Frequent Visitor

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
Community Champion
Community Champion

SAMPLE DATA PLEASE SIR

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Is this enough?

 

CountryCodes.PNG

 

 

PhoneNumbers.PNG

 

 

 

THIS WOULD BE OF GREAT HELP.

 

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

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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:

Capture.PNG

 

Regards,

Nick

 

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

 

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

can you send the link ?

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors