cancel
Showing results for 
Search instead for 
Did you mean: 
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
BhaveshPatel
Community Champion
Community Champion

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

7 REPLIES 7
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

 

 

 

BhaveshPatel
Community Champion
Community Champion

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

 

BhaveshPatel
Community Champion
Community Champion

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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!