Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 ?
User | Count |
---|---|
92 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |