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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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