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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors