Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Cyrilbrd
Helper IV
Helper IV

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

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
ImkeF
Super User
Super User

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

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))

 

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

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? 

 

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

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.

 

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

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

 

amitchandak
Super User
Super User

@ImkeF , can you help

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.