transform international phone numbers to country names

[MJOS-WVI]

I'm trying to create a map visual in a dashboard from a list of international phone numbers.

My phone numbers are in a standard format, like this:

+[countrycode][area code][phonenumber

Below is an edited sample of what that data in my column looks like:

International phone #

• +61421112233
• +818001112233
• +14165551111

I have a separate table of country codes and country names that I can match against ( so I know the first cell is Australia, the second is Japan, the third is Canada, etc.).

I need to isolate out the country code (which varies in length between 1 and 3 digits) from my column of international phone numbers. And then if country code is +61, then return "Australia", and if country code is +1 and area codes is 416, then return "Canada".

There is a way to use Power Query to transform the data, just need help.

I looked at this solution but it came up short.

Solved: Re: Match a phone number to a list of country code... - Microsoft Power BI Community

[v-henryk-mstf]

Hi @MJOS-WVI ,

I think the advice @AlexisOlson  provided is worth taking into account. If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your reply.

Best Regards,
Henry

[AlexisOlson · Accepted solution]

Here's the list of starting codes from the post you linked converted into M code you can paste into a new query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVrZcuM2Fv0VVD85Vd1VMnc+2vLSjpd4LLenkkweIAmm0CYBBSTlln9oKu/zB/1jc3EBaiGvnOUlHWO967nngvr11w+jDx8/PIhamJWYf/jt468fjpMsgrGTShg544pNeKW5mwkSnFFFK8tyM5bhWCOLljOu5uyUm2k776ajAKZP+YJXvGZHY11VWr0KXjYLpp9ZsxA/dAsTXGimfK5rNxZFx3ZMmGp7Hgp3amQj6wV7kqaQil3VJVzsN8VZCivGXPFuTzQKeyNpNuqNBGHSH4n7a9I0640ko6h/V3jcP2ewJh7170pHeW8kHwUDmfsjyXFfryQa6H7cvysK+2uCYKB71JcnHcW9kWzUPycfrAmy/jlh1Jc5TPq74uP+rizt78oGa6LjvnfirO+LOO3vio+HHuyfEw0iIQkHeg18miV9+4SR03Rd8V7Mprj2TFdSQc4N0sSnSIZe6VYp9iCW7bSUMz8bvDs7em82QvteGrGjYmqNd9nyqnOBVfBHSGPY75cgHNxq1QB8GN50G6217rSB3DaK3XIjwQadvu9BQI5WvW+FaTR7kDPdBX5KDWeJXT3hUjXsWjZNjeBzJ1YSLrkQcwESSa225kvjbLPhpp3JTV5Fm+EnqWYC/rUngVzMWUQq0fkpsEaZ2KW3nJtGKHZ01jazBVvCX52bEBEfDVh6zud41qOe8kJ3OWrlfmzNi5N4DAbV9X48hJG14RclGzFnk4Y3oiaxbrAI7OmBGxcE9pjzYr1s2NGJ4dON2zdWCY7tkgmIL9hMz4UfRDV1Cx6aAPSClsTGAJ1v9Gym2dG1VMVcVzvTNqJOygKEgYC+F3pZiv+0o1GQ1uxMVHpm3TOjBIoogWJqMEFDKll7XwbHKbXMuuNGTtfdopxYhJa65NXUSrsj1DY8A+d7oUTBS8ogARqEt1CbuLLHWFdVpI5BiEtLSZ4TYeJB1HFyGkHk+/8awebOoFcrLUEZai3WVAg1CRl4wWvth62Z7iT45qCuGMK60CVQg32wCBBnToWSZFiEo40VZFuTK46dP1xkEPMB5piFFHYjtPIeChGiFoAl/u+oU8Ie4xK+JGwdor0WkInUXYjakPJ278mzGWJj4MslZJXWtMauXkw1exJmTvohdFClAQgqgVl/D/Awk0tY/W4yIA6c/97yRoOaJTscFkiVLkEKNfQYcrCxVoU+5G+syrQkCIS42S/1HFCXtBzxJno/ncq65i25ytr9TAqQ6JKbDosDpC8Oij+LErD3IzupAZFrC+QIpEbWkF1sztm4VYtuGwK7WM8WooSAJS9EDwywbMcEWOQfXuEWUq/Y2ve8WUi93I23d70XYyTrChL93RCN0fpf5RQQl4SE2Nr8Wqg1LZo1+SNXbw52fEUg1lmjfykOapg6sGjVnBYiQ8B/syD5e0vGeUxBa2IN+8sQWv2e5BgBY84LXs+4IZdYO15ALYZCeyZsqa0gZV35fASckDY7wO+ASDZYr0AB8PNPM8GVP8Aa+BdZTfn0lRQcmcwdoPUhIWMH2PxV+gFryxtR62ZBVT/kc6e6qV8t96EOxJB95W/SlnPqBGSYuoICW6NPkcrsxGu6KdEOt6hbMqq4IyseDAbUYEgNUvU5o+pzllCDVInOMmqQCqZ89I8QIsfshUAxohuhFM4phXNK4ZxSOKcUzq3CJ6addhdbXS+40WKPywV57ii4UC4izoSquHlxnsSqaidn7lSsoXfCUmxPq7cBtI0RrKSnoixk61g81tALw1V3jFdNujwJEQMv5RRgquHGj9mr7oHOt8B7/JA996b9JipALFP4QXv2lRFWHj9iT7+a7Y7ESAqnDqgG8RoiQt3ay/3fWHvXS0MyiRAR6UKqnQuQm7RlwWlygfX+c6tgfu0GsFu5kc2iPSQUNkI3vFkdmLbGOK8bfWg7cj1dzvWKnsd6agDRDuyPnRP5ASNgX3ai5trypSPPK6DiNOudNalr1Lil6geWuObINWxKkzdZ4z7ZWgfLxnY3dh1uEqHmy4uBWOpGHF82NKSGWeCbR8uojfaDRAKGFOKEiDhjo0Ec+njMxVKvDtk1cxBd22mbcJ+FeQM+spKeYIYISpdGt0ubVDPdAkuEKvOR1QsQZb4rC3Z0UHkuNPjRsJ/bQkNqr/Zo1C2fibkNErfHbrlqeOnCEHneAzCFbh7p3ORVNm8uw23frJ6HXW2Ejcv4TUBp3CN9kWtXwAD8pT9DQF+ELcmNhHPAIXUjLLsnIyUKCHdEAYGHUUDgYRQQBSAKiAIQBUQBQKJ60tbgCm8oK42nPDsYCFjJG2bfCSEg3ctA9yKxi1HIVj3SugEU+RUM7SARGSlsfeXeURmioXPJIKiQZV4KA35cv0P44tEIa0D5gudsnkYA+Wz41d0q9/pZyjfhBwLk1pBzQCmpoI7xpfO8ZBM4iQMkkGusxT5rNW8NpxAlxme8O8hyw4uW+zFslDSUVvsE0w3iwwwwnIoWZvvecm/bOdf53FryWNpWaqyBrc8auZLN9z9Y0xE5XkJnKxgo+PD9DzzTss1nqFrf/8tlLbrTra0/c0kyZle0hGndXwg14lv3doSlatxV5NgBcAGUErBrP1ewYJ0aS9O2j0orQbgU7bOQpXcVFi5d6gOsFyvXEyDfG9gCFpzqUq7wpYw4GjkPuH0OZmttyzigwjtrMWbsafjsUkIfrhAxIA4RrXeWumhadx11jOznfNbawPEj0ZZ4Q0+HXPa9y2OMBwybNaV17uq7AUO7FuK9wxAnUP6K4uwxEqkvpnWXnXMALUOaDzkBWEAa8ZHh4905AAjH9wkbkRPeRQIuHUNeQKS5UElGnvivu0cm7FcQgWxb54asJaHrsL036W7sP+4hOuRyaV8TD/SgrgsRr+wXwTcQha0HSF3wpSZfebAVeVxwud2CpOZAO/8ooaf4BK2Lr9zuobcHswmymo2Wip1/s9aFINrpt/zKEFOkVUKyM0tSapC98nOurYKekpI7deGybDmzSrtHAz+FOmlQetgaJalrfSC59P57aIIk5okrgEj6xhwJ41cKMhJkMPcgPLkV2cy/eVlKFzQXbdPadPD2sA9wG4jqHuagOzPiEzCC7gz3DqNf9qVG0nMnW296iu4krsGyn+LYEUSaWAr4D0Tyfk4nyHqupZFTTuJigqzHWnsMMDt3nPVvqYAPg+2Kl60fyLcQAXVx7VPg75yJ+PaoXwAJ/aGIYrdyZrqM8tC7+9DdbXavrgZIWVluSymdX1R/l1D9XUL1dwnV3yU5wWeSnOAzSU7wGbv5AXLG5tjORwsbYdf8jb8ssJkdOjJDCnGlMCs9voMXxHIBJmPAulfS93jZiMjvbERwwGxEmCcbEebJRoR5shFhHveVcDCYDYSfOFJtKUYnPkT6ZDzp9CWMhzn5I1/6Zx5Mr2sASQqCs5B4C8lCyjYhZZuQsk1I2SakbBNStgkp24RE6GQhpb29/EmKht3xih1N9AyokwTzDXWPe/Vg/3PMbq6g+fwmyjZIpYA8FrBSFR8ZUB7VrXcfNWZc7w9TNordl9BqqueSgPgM6dQN1+xPPxz59ZQlY8qS1BNl5smbGnypGpoydXkHDUMNDcbk7mTsxylrpVQkpVQkpZSVsEB2P85gn7A1bySwoVs9Baa5yZOT5RJkxEyq/c7kH+9M//HOzHViKwGmKcFzptaOMQDL11XVKr9+m99f7h87s+a71z5D57JBBqXVJ7tf2O8EJVu2Zqlrzz4yLNqnXBUl0ON68ef+Q8y4LPUUjupUgooCdR3q6GQNrAhy6fJ2Mvlh2OhnWTDArTvRvGrzQjwLZFjX//pqKgKod9UMw/WRy1eu9jKNelrNXMHuu6BGE5/JGlkzmKmU4hmq9tlDZyYqTXIKQnMq8KnX1YyqvhlVfTOq+mZU9c2o6ptR1Tejqq9jHq15Ea67R13w8wFRQVCne/7i3pcPf91FNU+eiwVXuNQNoppGshuuXvjeV793gBvtcAsdWmU/jHQfAfxc15rMIeOoPj7HTuVGTDlkkB+xKvyoDX4G+8zrhahs2PfhN8d+ZbLGfnTvdwN+Gh92Df+dvNXKfN2+QmcORWmPnubYqkx4O5d4LAX9OX46+VlUgqI9OX43+cn+gOZo0pZg3f3z91DkEzP4vw50Zq0BltqUa9YCdTZMKjBbIwucddvTwY/h8nTnNxZoifNKWiJa+2kEhNpwUQ6UdY0RX9gnUUJPRPx/8cZ6tr8TXx0WLcbZcCM20faDbNd/5qlj9Ute/rUvk3lKZEiOGLyPV/cGIqSt2IOVb0vL7h88LcNMg0h4Nx8o2Mgx1R7xozbQDs6WRltYnzMFzYncE6IZFBB/w0yDS+ultq0QyFU7+YbYmmPqPvKvskteQsjQQ4F9iN+mLebtyZswUy6/HtiJX7yFNkXnDkSq67Up1m+9zKHgKncPGW9T8Y50g/L4DP0nnFK4SlZ3hP+3/wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, Country = _t])
in
    Source

From here, we can define a new custom column that uses these codes:

(r) => Table.SelectRows(
           AreaCodes,
           each Text.StartsWith(
                    Text.AfterDelimiter(r[#"International phone #"], "+"),
                    [Start]
                )
       )[Country]{0}

This takes the first (index 0) row of the Country column after filtering the AreaCodes table (above) such that the phone number after the "+" starts with the code in the [Start] column.

Sample query including this custom column and referencing the first table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jYzNDEyNDQ0MjI2VorVAQpYGFoYGKCIGJoYmpmamgLFDJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"International phone #" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Country", (r) => Table.SelectRows(AreaCodes, each Text.StartsWith(Text.AfterDelimiter(r[#"International phone #"],"+"), [Start]))[Country]{0}, type text)
in
    #"Added Custom"

[MJOS-WVI]

This seems like it could work, and I understand (mostly) the code. Unfortunately I was unable to produce the same results in a new Query with my dataset, though I am admittedly a PowerBI novice. If only I could call someone to walk me through this...