Replace text in a simple column with a given text string in one single query

[Anonymous]

Guys,

I have a column where i need to replace some texts with one single text, it is a project name column and i need to merge diferent names by a single standard name. example:

Project_A_BR -> ProjectA-BR
ProjeactA-BR -> ProjectA-BR
Project-ABR -> ProjectA-BR

The text values are in a single collumn

I can use a text replace function to do it, but i was looking for a more "elegant" solution, anyonw can help me?

[MartynRamsden]

Hi @Anonymous 

Given that you've posted this in the 'DAX Commands and Tips' forum, my answer explains one way to do this in DAX.

However, I would reccommend that you fix this column in the Query Editor during data load.

There are various ways to achieve this in DAX but I'm assuming you don't want to hardcode the incorrect values into your expression (in an IF statement, for example)?

One alternative approach is to use a series of nested SUBSTITUTE functions to remove any unnecessary characters or incorrect spellings.

Then use the REPLACE function to add the "-".

Correct Project Name = 
REPLACE ( 
    SUBSTITUTE ( 
        SUBSTITUTE ( 
            SUBSTITUTE ( Table1[Name], "_", "" ),
            "-", ""
        ),
        "Projeact", "Project"
    ), 
    9, 0, "-"
)

Doesn't look very elegant to me... but it works! 🙂

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[Anonymous]

HI Martyn,

Thanks for your reply.

Your solution could fit in some cases, but i was looking for my data and it does not fit, i will explain with a real example, hope you can help

I have a column called Project, and there are some variations of same project, ex:

CRM CITRIX BR
CRM CITRIX PROD BR
CRM CITRIX 2 PROD BR
CRM VITRIX BRAZIL

i did a cleanup on the project data and merged the above names in just one :

CRM CITRIX - BR

the previous data from months before the cleanup are with wrong variations and i need to replace it to have the costs for project CRM CITRIX - BR in the unique Value to create the reports.

Do you think there is a way to do this replace?

[MartynRamsden · Accepted solution]

Hi @Anonymous 

If you only have those 4 variations, the easiest way to do this in DAX would be with the SWITCH function, as follows:

Calculated Column =
SWITCH (
    TRUE(),
    Table1[ColumnName] = "CRM CITRIX BR", "CRM CITRIX - BR",
    Table1[ColumnName] = "CRM CITRIX PROD BR", "CRM CITRIX - BR",
    Table1[ColumnName] = "CRM CITRIX 2 PROD BR", "CRM CITRIX - BR",
    Table1[ColumnName] = "CRM VITRIX BRAZIL", "CRM CITRIX - BR",
    Table1[ColumnName]
)

You could achieve the same result in Power Query using Add Column>>Conditional Column.

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.

[Anonymous]

HI Martyn,

Thanks, i guess now its fit perfectly to my need!

I am getting this error when trying to add a column with DAX that you suggestes

Error > Argument '14' in SWITCH funtion is required.

[Anonymous]

HI Martyn

i found the problem, "Unknow" parameter at the end of DAX was missing now worked

ProjectAfterCleanup =

SWITCH (

TRUE (),

China2019Costs[Project] = "Active Directory", "Cloud CoE - Active Directory - GLOBAL",

China2019Costs[Project] = "AF Knowledge Platform", "AF Knowledge Platform - CN",

China2019Costs[Project] = "AFL Paging seal Platform Prod Country China", "AFL Paging seal Platform - CN",

China2019Costs[Project] = "AFL SLIM", "SLIM AFL - CN",

China2019Costs[Project] = "AFL SLIM Taskman", "SLIM AFL - CN",

China2019Costs[Project] = "Auto Vehicle Inspection Management Tool Prod Country China", "Auto Vehicle Inspection Management Tool - CN",

"unknow"

)