Return value in column if it contains a specific value, if not return value in a different column

[S_Harmon]

Hi,

Im trying to create the "New Department" column where it looks for "Machine Shop" or "Charge Shop" in the Supplier column and returns those values if it contains one of those values. If it doesnt, it needs to return the value from the "Department" Column.

Can anyone please help?

Date	NCR	NCR TYPE	WO	PO	SUPPLIER	PART NUMBER	QUANTITY	DEPARTMENT		New Department
8/1/2024	1	Internal	101			ABC 1	1	Assembly		Assembly
8/1/2024	2	Internal	102		Machine Shop	ABC 2	3	Assembly		Machine Shop
8/10/2024	3	Internal	103		Machine Shop	ABC 1	7	Assembly		Machine Shop
8/15/2024	4	Supplier		P101	Supplier X	ABC 5	4	Receiving		Receiving
8/15/2024	5	Internal	104			ABC 2	8	Assembly		Assembly
8/15/2024	6	Supplier		P102	Supplier X	ABC 4	5	Assembly		Assembly
8/15/2024	7	Supplier		P103	Supplier X	ABC 2	3	Receiving		Receiving
8/20/2024	8	Internal	105			ABC 3	6	Assembly		Assembly
8/25/2024	9	Internal	106			ABC 3	2	Assembly		Assembly
8/30/2024	10	Internal	107		Charge Shop	ABC 4	3	Assembly		Charge Shop

[rsbin · Accepted solution]

@S_Harmon ,

Please try this as a Calculated Column in DAX:

NewDepartment = SWITCH(
                    TRUE(),
                [Supplier] IN { "Machine Shop", "Charge Shop" }, [Supplier],
                [Department] )

 Else you could do an if function in Power Query as well.

Regards,

[S_Harmon]

That worked! Thank you so much! I was way over thinking that for some reason.