cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

split text if starts with "M" with all caps (3-6 characters)

I have a column with data I need to extract certain text. (sample data is attached)

If the "application" column starts with capital M (and 3-6 characters, mostly 6 but sometimes 3). I need this to go in a seperate column if it's there and if not, be left blank.

I am also struggling with those which have 2 applications, would end up with each of these on a seperate line and all the other data on that oroginal row just duplication on the 2nd one. 

electrobrit_0-1599668163932.png

 

This ultimately is an important filter column so trying to extract the data.
It's stumped me for a couple days now. Help is appreciated! 

Sample data 

14 REPLIES 14
Highlighted

@ImkeF @edhans @HotChilli 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

I copied Greg's algorithm. Add these in the Advanced Editor and swap #"Removed Columns" for your previous step 

 

#"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Columns", "Text Before Delimiter", each Text.BeforeDelimiter([Application], " "), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text Before Delimiter","-","",Replacer.ReplaceText,{"Text Before Delimiter"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if Text.StartsWith([Text Before Delimiter], "M") then [Text Before Delimiter] else "", type text)
in
    #"Added Custom"

 

Highlighted

@HotChilli  thanks for looking and trying to offer a solution.

It didn't work in advanced editor (there are a lot of steps before and I kept getting errors such as "_ already referenced, did you miss a module?". So I tried to move the steps up to see if I could avoid the error, still error.
 I changed numbering of the steps it seemed to work but the custom column produced an error.

I finally tried to do it in steps (insert text before delimiter (space), replace value, then added custom) but the custom column says "False". 
I spent a long time trying to figure it out but to no avail.

 

error in custom column.png

Highlighted

Get rid of everything before the 'if' and remove ", type text)" .  That is Advanced Editor code, so it includes a name for the current step, a reference to the previous step , the action requested and finally the code to perform it.

 

I realise this will be challenging for you but if you want to learn to use Power Query, learning the difference between 'point-and-click' features, M language and debugging in the advanced editor will be helpful skills. 

View solution in original post

Highlighted

it worked! Appreciate your help. Yes, this is an area I need to learn better.

thanks @Greg_Deckler and @HotChilli both Dax and Power Query solutions worked great and I have them in my toolbox now. 
So helpful to see how each work an does the same thing for different requirements in the report.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors