…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…


Image credit: 9gag.com 

One thought on “The Power of M…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.