Remove portion of Text from 1 table column that is already another complete column text

[glmoon5]

I have 2 columns in a 'Combined Data' table:

Category

Class

The 2 columns look like this:

Category	Class
BREAKFAST BREAD	BREAKFAST BREAD ENGLISH MUFFIN
BREAKFAST BREAD	BREAKFAST BREAD LOAF BREAD
BREAKFAST BREAD	BREAKFAST BREAD SPECIALTY BAGEL
BREAKFAST BREAD	BREAKFAST BREAD TRADITIONAL BAGEL
BUNS & ROLLS	BUNS & ROLLS BFY
BUNS & ROLLS	BUNS & ROLLS BREADSTICKS -NS
BUNS & ROLLS	BUNS & ROLLS SPECAILTY
BUNS & ROLLS	BUNS & ROLLS TRADITIONAL
LOAF BREAD	LOAF BREAD FRENCH -NS
LOAF BREAD	LOAF BREAD SANDWICH
LOAF BREAD	LOAF BREAD SOURDOUGH -NS
LOAF BREAD	LOAF BREAD VALUE ADDED
LOAF BREAD	LOAF BREAD VARIETY

As you can see the class column has the category name at the beginning of the "actual" Class Name I want to display and I am looking for a way to have the query remove it from the Class column.

[kushanNa · Accepted solution]

Hi @glmoon5 

you can do this in Power query side or in DAX side 

please use the following prefered method you like 

If you're in Power Query (Transform Data):
You can create a new column like this:

Go to Transform Data.

Add a Custom Column with the following formula:

Text.Trim(Text.Replace([Class], [Category], ""))

This replaces the category name in the class and then trims any leading/trailing spaces.

If you're in DAX (Calculated Column):
Use this DAX formula to create a new column:

CleanClass =
TRIM(
SUBSTITUTE('Combined Data'[Class], 'Combined Data'[Category], "")
)

This will also remove the category from the beginning and clean up spaces.

[rosha_rosha]

Exactly, as always the same approach and the same concept for DAX, SQL and M languages but with slightly different syntax

[rosha_rosha]

1. It is in Sql Server

SELECT
Category,
TRIM(REPLACE(Class, Category, '')) AS  Class FROM
[Combined Data];

UPDATE [Combined Data]
SET Class = TRIM(REPLACE(Class, Category, ''));

2. It is in Dax

Cleaned Class =
TRIM(
SUBSTITUTE(
'Combined Data'[Class],
'Combined Data'[Category],
""
)
)

If it works out for you, accept it as SOLUTION so other users can also benefit from it!