Replace text in cell if cells in other columns contain specific text

[wackojacko1979]

Hi All,

First time poster, very new to Power BI and Power Query.

My issue is as follows:

Due to incorrect data entry (outside of my control), some cells are showing an incorrect value. See CorrectedPackage column.

I wish to search 3 seperate columns, Package.5, Package.6 and Package.7, for multiple specific text values, (COLO, BAS, AHU, etc).

If these values are returned, I wish to replace current text, MECH, in CorrectedPackage column with BAS.

I have done some searching, and there seems to be multiple options that will get me close, but nothing I have found that works correctly, or that I can edit (clumsily) to make work!

Thanks in Advance!

[ITManuel]

Hi @wackojacko1979 ,

welcome to the community.

If I understood you correctly:

1. Merge Package5. Package6. and Package7. into one column only --> ( This will make life easier for searching specific text in 1 column only rater than in 3. I have named the column " Package567"

2. Add calculated column "CorrectedPackage" with DAX and use the following code 

CorrectedPackage = 
SWITCH ( 
    TRUE (),
    CONTAINSSTRING ( 'Table'[Package567], "COLO" ), "BAS",
    CONTAINSSTRING ( 'Table'[Package567], "BAS" ), "BAS",
    CONTAINSSTRING ( 'Table'[Package567], "AHU" ), "BAS",
    CONTAINSSTRING ( 'Table'[Package567], "Any other value you want to check" ), "BAS",
    'Table'[Package]
)

Hope this helps.

Best regards