Forum Discussion
Messy Column. in Query editor can I pull out data thats two Capital letters and then a Word?
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
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
3 Replies
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.
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
Transformation -> Split...by Delimiter -> Split at...each occurrence of delimiter
