topic Re: Replace Column Values in DAX Commands and Tips

Replace Column Values

dhodgson — Tue, 11 Apr 2023 21:59:33 GMT

Hi,

I need to be able to replace all the values in the 'Trade Engagement' column to look like the below when 'EmployeeID' is NOT "1.Subcontractor" or blank.

EmployeeID	Trade Engagement
CARL	Company
1.Subcontractor	1.Subcontractor
ADAM	Company

JAMES	Company

Thanks in advance

Re: Replace Column Values

FreemanZ — Wed, 12 Apr 2023 00:18:47 GMT

hi dhodgson

not very clear. do you mean something like:

Column =

IF(

[EmployeeID] IN {"1.Subcontractor", BLANK()},

[Trade Engagement],

"Company"

)

Re: Replace Column Values

dhodgson — Wed, 12 Apr 2023 01:54:42 GMT

That forumla isn't wokring. All I did was duplicate the EmployeeID column and renamed to 'trade engagement'. I then need to replace all values to 'Company' if they are not 1.subcontractor or a blank field. I tried using the below, however it replaces all text with "Company";

= Table.ReplaceValue(#"Renamed Columns1", each [Trade Engagement], each if [employeeid] <> "1.SUBCONTRACTOR" then "Company" else [Trade Engagement], Replacer.ReplaceText,{"Trade Engagement"})

Re: Replace Column Values

FreemanZ — Wed, 12 Apr 2023 03:02:47 GMT

hi dhodgson

try to add a custom column like:

= Table.AddColumn(#"Changed Type", "Custom", each if [EmployeeID] ="1.Subcontractor" or [EmployeeID]=" "
then [EmployeeID]
else "Company")

it worked like:

Re: Replace Column Values

FreemanZ — Wed, 12 Apr 2023 03:03:43 GMT

btw, the first reply is for DAX, it works like:

Re: Replace Column Values

dhodgson — Wed, 12 Apr 2023 06:10:43 GMT

Apoloigies, was definitley doing this one wrong! This helps. Appreciate it.