Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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
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.....
SAMPLE DATA PLEASE SIR
Is this enough?
THIS WOULD BE OF GREAT HELP.
https://www.powerquery.training/portfolio/replicate-excels-vlookup-function/
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:
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
can you send the link ?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
77 | |
60 | |
52 |
User | Count |
---|---|
165 | |
86 | |
68 | |
68 | |
58 |