…Power Query M
WARNING: convoluted geek joke ahead
Three data analysts walk into a bar, and the bartender gives them a problem:
“My data table includes a column containing Company Name, but I want the business acronyms in a new column, with a space between each initial. Oh, and the data contains some hinky characters and has been entered onto the system creatively. What’s your best solution?”
The old-school procedural programmer suggests some form of do…while loop iterating over all the rows, extracting each sub-string and concatenating each left-most character, with a single space between each.
The analyst with a love of Power Query in Excel comes up with a two solutions: the first involves transforming each string into a list, removing the unwanted characters, adding in the spaces and returning the list to a string.
The second, cleaner, solution from this analyst first splits out each sub-string into a new column, extracts the first 1 characters from each sub-string, and transforms them to uppercase. Then defining each new column as a variable and testing its Unicode value is within the correct range, and finally concatenating each variable, with a space between each.
The Power BI Developer simply suggests a single line of code:
Text.Replace (Text.Remove (Text.Replace (Text.Proper (Text.Remove (Text.Trim (Text.Clean ([Company Name])), {“‘”})),” And “,” “), {“a”..”z”,”0″..”9″,”(“,”)”,”,”,”.”,”-“,”&”,”£”}), ” “,” “)
An in-depth look at this in a post coming soon…
EKlär Solutions 
One thought on “The Power of M…”