Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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?
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
@ImkeF , can you help
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |