I saw similar problem posted in Power BI Community and thought this is worthy of a post
Here is the sample data
| Countries+DialCodes |
| China86,India91,United States1,Indonesia62 |
| Brazil55,Pakistan92,Bangladesh880,Nigeria234 |
| Russia7,Japan81,Mexico52,Philippines63 |
And our objective is to extract Countries and Dial Codes in separate Columns
So here is the final desired output
Easy Peasy …. We can add a new Custom Column from the Query Editor and use the Text.Select “M” function to extract the Countries as follows
Similarly to get the “Codes” Column we can use following Custom Column
THE REAL CHALLENGE
The above problem was made easy because the delimiter i.e comma “,” already existed in the data.
What if our data looked like this and we need the same desired output as above
| Countries+DialCodes |
| China86India91United States1Indonesia62 |
| Brazil55Pakistan92Bangladesh880Nigeria234 |
| Russia7Japan81Mexico52Philippines63 |
If we use the above Custom Columns using Text.Select we will get the following result
Our challenge is to insert a delimiter/comma between Countries and their Codes for which we can not use Text.Select. We have to use a different approach
In this case our first step will be to create 2 lists….One list is the list of characters to remove i.e. numbers from 0 to 9 and other list containing replacement for each of these numbers i.e. a delimiter (|)
Go to the Advanced Editor and create these 2 lists as follow
CharsToRemove = {"0".."9"},
CharToReplace=List.Repeat({"|"},List.Count(CharsToRemove)),Our next step is to convert each Text i.e. “Countries + Dial Codes” into a list of individual characters using Text.ToList function
Then we can replace the “number” characters in that list with a delimiter of our choice (in this example “|”) using List.ReplaceMatchingItems function
Then we can combine the list back to Text again
So the Code for new Custom Column for above step and the resulting output looks like this
=Text.Combine(List.ReplaceMatchingItems(
Text.ToList([#"Countries+DialCodes"]),
List.Zip({CharsToRemove,CharToReplace})
)
)
Next Step is to split text into a list using delimiter (i.e. “|”) >>> remove the null values in the list>>>Recombine the list using Text.List
So the Code for new Custom Column for above step and the resulting output looks like this
Text.Combine(
List.Distinct(
List.RemoveItems(Text.Split([Custom],"|"),
{null,""})
)
,
",")
Click here to download the Power BI file and see and play with the above examples