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

Text.Remove

Given a series of model code such as:

BONH-P-M-16

CAN-BL-A17

88-R-AC-19

Said column also contain blanks.

New calculated column need to return the following:

BONH-P-M

CAN-BL-A

88-R-AC

Present formula used is:

= Table.AddColumn(framework_tblsalesitem, "model_code(", each Text.Trim(Text.Start(Text.Remove([product_name],{"0","1","2","3","4","5","6","7","8","9"}),Text.PositionOf(Text.Remove([product_name],{"0","1","2","3","4","5","6","7","8","9"}),"-",Occurrence.Last))))

 

Issue encountered:

All numbers are removed, yet only the last string shoudl be removed.

88-R-AC-19 gives -R-AC but we need to see 88-R-AC

All blanks should return a "no data" Maybe with TRY OTHERWISE? not sure of the proper syntax.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Text.Remove

Hi @Cyrilbrd 

if you want to do this via the UI, you add a custom column and paste in this formula:

 

Text.TrimEnd([product_name], {"0".."9"} & {"-"})

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

9 REPLIES 9
Highlighted
Super User IV
Super User IV

Re: Text.Remove

@ImkeF , can you help

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User III
Super User III

Re: Text.Remove

Hi @Cyrilbrd  

there is an optional parameter in the Text.TrimEnd function that allows you to pass a list of characters that shall be removed from the end of the string:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL389AN0PXVNTRTitWJVnJ29NN18tF1NDQHc8GEhYVukK6js66hpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [product_name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product_name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.TrimEnd([product_name], {"0".."9"} & {"-"}))
in
    #"Added Custom"

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper III
Helper III

Re: Text.Remove

Thank you for your reply, did not try yet, would not have found this by myself either.

Will this modify the Column Product_name or will it create as required a new Column Model_Code( ?

What is that part doing exactly?

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvL389AN0PXVNTRTitWJVnJ29NN18tF1NDQHc8GEhYVukK6js66hpVJsLAA=", BinaryEncoding.Base64), Compression.Deflate))

 

Highlighted
Super User III
Super User III

Re: Text.Remove

Hi @Cyrilbrd ,

this just creates sample data. (It is the resulting code when you enter data manually in Power BI).

Replace it with the reference to your table.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper III
Helper III

Re: Text.Remove

Would that be correct?

let
  Source = Table.FromRows(framework_tblsalesitem), let _t = ((type text) meta [Serialized.Text = true]) in type table [product_name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"product_name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "model_code(", each Text.TrimEnd([product_name], {"0".."9"} & {"-"}))
in
    #"Added Custom"

How should I generate this column? Should I still use add column? 

 

Highlighted
Super User III
Super User III

Re: Text.Remove

Hi @Cyrilbrd ,

if the code you've posted doesn't work, please post the error-message you see.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted
Helper III
Helper III

Re: Text.Remove

Sorry but I feel you misunderstood my question.

How do I apply your solution?

Once in Power Query Editor, what shall I do? 

Insert a Column?

q1.PNG

Thanks.

 

Highlighted
Community Support
Community Support

Re: Text.Remove

Hi, @Cyrilbrd 

 

As is suggested by @ImkeF , you may go to Query Edior, click 'Advanced Editor', add a new step like below and replace the column name and step name with yours.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.TrimEnd([product_name], {"0".."9"} & {"-"}))

 

Best Regards

Allan

 

Highlighted
Super User III
Super User III

Re: Text.Remove

Hi @Cyrilbrd 

if you want to do this via the UI, you add a custom column and paste in this formula:

 

Text.TrimEnd([product_name], {"0".."9"} & {"-"})

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

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

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors