Dax to Remove certain characters from column

[icdns]

Hi, 

Would like to ask for your assistance to removing certain characters from column. Just like to remove characters on the first part of the word. 

Below is my example. 

From this: 

DIM_COMPLAINT_TYPE

ALL_ALL_PostPaid Service

ALL_CF_Customer Feedback

ALL_INQ_Application

ALL_INQ_AF_Billing

ALL_INQ_CF_Products

ALL_INQ_General Inquiry

ALL_REQ_Billing Enrollment

ALL_REQ_CF_Sim Concerns

ALL_REQ_CF_Other General Support

ALL_REQ_Payment Update

ALL_REQ_Service Maintenance

To this: 

DIM_COMPLAINT_TYPE

PostPaid Service

Customer Feedback

Application

Billing

Products

General Inquiry

Billing Enrollment

Sim Concerns

Other General Support

Payment Update

Service Maintenance

Thank you!

[Anonymous · Accepted solution]

Try this- 1. Go to Power Query

2. Find Extract under Transform tab-

3. Select Text after delimiter

4. Provide the inputs as below and you can see your desired output in snap below-

[icdns]

This one worked! Thank you so much! 

[amitchandak]

@icdns , Tried same and created two column and one has what you need ( Check Attached file after signature - Table 4)

Step

 Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DIM_COMPLAINT_TYPE.1", type text}, {"DIM_COMPLAINT_TYPE.2", type text}})

 Complete script

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZDRDoIwDEV/ZeHZn0AihgQVJD4RQuZotHF0cysm/L2gQjIf+nJPc9rcuo7iPG/nKYznQmInKnAvVBA1my9M0jYZPJsenEgBuqtUjxVmx7KNrdWoJKOhME/bLWqNdAviyVc40w2KfZDvgcBJLTJ6DujGlZ135eIRO3JG6x6IRcAnZ4W9SAwpcOT/2Ynv0/PLgWqw1jgOlgo5fqwX20mGAP36EAeJxECS5m6aNw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DIM_COMPLAINT_TYPE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DIM_COMPLAINT_TYPE", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "DIM_COMPLAINT_TYPE", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"DIM_COMPLAINT_TYPE.1", "DIM_COMPLAINT_TYPE.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"DIM_COMPLAINT_TYPE.1", type text}, {"DIM_COMPLAINT_TYPE.2", type text}})
in
    #"Changed Type1"

[amitchandak]

@icdns , In power Query, try Split by delimiter on the right click of the column.  Try with "_" and right most option

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[icdns]

Hi @amitchandak 

It worked! But there are words with no "_" are affected. 😞 

Thank you!