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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors