Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 ?
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
94 | |
88 | |
35 | |
35 |
User | Count |
---|---|
154 | |
100 | |
82 | |
63 | |
53 |