Removing character from beginning Len vary

[KevinMorneault]

Hi

I am trying to improve the quality of our report. One thing i trying to do is remove any character from beginning to the string needed. Len would vary as  there is non -standard process to enter the summay here's an example

Scenario 1

1234533321122New IT Equipment

33Account setup

test1test2upgrade IT equipment

Basically here i would like to remove everything before  New IT Equipment, Account Setup and Upgrade IT equiment

By doing this i would have a colum for Type of Request  within the colum end result would be like this

Type of Request

New IT Equipment

Account Setup

Upgrade IT Equipment

Instead of scenario 1 .

Main reason of doing this would be for reporting and filtering within a Dashboard, which right now we are not able to do 

[v-xiaotang]

Hi @KevinMorneault 

I just want to confirm if you resolved this issue? If yes, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

If you need more help, please let me know.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[tackytechtom]

Hi @KevinMorneault ,

You mean something like this?

Here the code in M

if Text.PositionOf([Column1], "New", Occurrence.Last) > 0 
then Text.RemoveRange([Column1],0, Text.PositionOf([Column1], "New", Occurrence.Last)) 
else if Text.PositionOf([Column1], "Account", Occurrence.Last) > 0 
then Text.RemoveRange([Column1],0, Text.PositionOf([Column1], "Account", Occurrence.Last))  
else if Text.PositionOf([Column1], "upgrade", Occurrence.Last) > 0 
then Text.RemoveRange([Column1],0, Text.PositionOf([Column1], "upgrade", Occurrence.Last))  
else [Column1]

Unfortunately, you cannot use the function PositionOfAny() since it allows only single letters / numbers in the list. But you can use PositionOf and then do a nested if statement. Note, M is case sensitive, meaning in our example above you must look for "New", "Account" and "ugrade" to get a match.

Hope this helps and do not forget to mark the answer as a solution if that was what you were looking for 🙂 Others will have it a lot easier to navigate to the solution then.

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @KevinMorneault ,

It is hard to tell which logic is needed to fix your issue for all eventualities. I will still give it a shot though. I came up with the following rule:

remove everything before and including the last number of the string

1234533321122New IT Equipment

33Account setup

test1test2upgrade IT equipment

-->

1234533321122New IT Equipment

33Account setup

test1test2upgrade IT equipment

-->

New IT Equipment

Account Setup

Upgrade IT Equipment

Here a solution in Power Query:

Text.RemoveRange([Column1],0, Text.PositionOfAny([Column1], {"0".."9"}, Occurrence.Last) + 1)

And here the code in M that you can paste into advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjE1NjY2MjQ0MvJLLVfwDFFwLSzNLMhNzStRitWJVjI2dkxOzi/NK1EoTi0pLQCLlaQWlxiCCKPSgvSixJRUkLZUhLZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.RemoveRange([Column1],0, Text.PositionOfAny([Column1], {"0".."9"}, Occurrence.Last) + 1))
in
    #"Added Custom"

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[KevinMorneault]

Hi 

How are you tks for your input. I guess i did not notice that i provided you only number. As for the scenario or example provided, i just type in some character in front of the string we are trying to keep, so basically in most case it would be a letter or - (Hypen), what i did, i did some modification with your query by changing numbers with letter, when i did, it was only showing the end of the line, so i change occurence.last with occurence.first 

One thing i wanted to ask if i know the beginning of the string examle New, Account, Upgrade etc, is there a way to have a query stating everytying before the beginning of the string to be remove. so for example if i have  Xerox inc montreal - New , everything before new would be remove.