Messy Column. in Query editor can I pull out data thats two Capital letters and then a Word?

[DebbieE]

I have some data that contains a column with lots of information. I think it needs splitting up into more columns but its not simple to deal with.

All the detail is split with -

In some cases I know that I am looking for region. This can be added to a region column. 

Region is for example - AU Central.

Id be able to do this in SQL but how can I 

Create a new column using data that starts with - CAPITAL LETER CAPITAL LETTER  then anything until we get to -

Annoyingly the column can have none to 4 - so there is no way of knowing where this info is. Just that it starts with two capital letters for the code

Any Help would be very much appreciated

Debbie

[jstorm]

Transformation -> Split...by Delimiter -> Split at...each occurrence of delimiter

[kentyler]

There are no regular expression functions in M, so you have to work around it.

Do you know in advance what the "region" abbreviations are ?

You could split the columns on the "-" and then compare the first 2 letters with your list of regions.

[DebbieE · Accepted solution]

Unfortunately not. The only thing I have is that the code is two capital letters.

I think what I would have to do then in this situation is actually import into SQL and do some cleaning with a bit of SQL Scripting first before moving into power BI