How to remove certain text from the subject line

[Anonymous]

I have a two subjects like "${Recipient.lead.companyname[0]!""} | Efficiency meets affordability with Microsoft 365 for nonprofits" and  "Efficiency meets affordability ${Recipient.lead.companyname[0]!""} | with Microsoft 365 for nonprofits". I have to remove this text "${Recipient.lead.companyname[0]!""} |" and show remaing text.

Kindly help me to achecive this.

Thanks & Regards,

Vamsi Krishna

[Idrissshatila]

Hello @Anonymous ,

one of the things that you can do is to right click on the value in power query and replace it with the value you want.

https://learn.microsoft.com/en-us/power-query/replace-values

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Anonymous]

Thanks for your response. Can we acheive this with any dax formula.

[Tahreem24]

@Anonymous Try this DAX column:

Output = MID('Subject Table'[Subject],SEARCH("|",'Subject Table'[Subject],1,0)+1,LEN('Subject Table'[Subject]))

[Anonymous]

In the screenshot for second subject in output this text "Efficiency meets affordability" is missing.

[Tahreem24]

@Anonymous Try creating a column like below:

Output =

VAR Res1 = SUBSTITUTE(SUBSTITUTE('Subject Table'[Subject],"""${Recipient.lead.companyname[0]!"""""," "),"} |"," ")

RETURN SUBSTITUTE(res1,""""," ")

[Anonymous · Accepted solution]

Hi Tahreem,

Thanks for your reply. I have used replace option in transform data then my problem is solved.

[Idrissshatila]

Hello @Anonymous ,

yes, but you should create a new column that says the following:

new column =
IF (
columnName = "${Recipient.lead.companyname[0]!""} | Efficiency meets affordability with Microsoft 365 for nonprofits",
"Efficiency meets affordability with Microsoft 365 for nonprofits",
IF (
columnName = "Efficiency meets affordability ${Recipient.lead.companyname[0]!""} | with Microsoft 365 for nonprofits",
" with Microsoft 365 for nonprofits",
columnName
)
)

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Anonymous]

We have many many different subjects like that. It's difficult to add each time when we get new subject to formula. Is there a way by automatically removing this piece of text "${Recipient.lead.companyname[0]!""} |".