Change value based on value of another field

[dieffen]

I have a field called Entity that is joined to another table in my Power Pivot data model. I want to change the value of the value from "113" to "163" if another field for the same record in the same table is a "HCWB" or "DSC". Can this be done on the import or do i need to add a custom column, make the change, copy over to the Entity field to maintain my data model and then delete custom field? If Elseif Then?

I can see myself making updates to a field based on the value of another. Along with hopefully the answer to my question above, could I get the general M syntax to change/update the field. I had looked at the Table.ReplaceValue() function but not sure I understand the parameters or the correct format of selecting field/column names.

Thanks for any help,
Eric

 

[dieffen]

[FONT=&quot]Sorry for not being too clear. Was trying to be as concise as possible. The source is a csv file. The Entity field contains the 113, 153, 163, .... values. Another field in the same file called Location has the "DSC" and "HCWB" and other values in it. For only the records that have "DSC" or "HCWB" in the Location field, I want to change the Entity value from 113 to 163.[/FONT]
[FONT=&quot]Hopefully that clears up a little.[/FONT]
[FONT=&quot]thanks,[/FONT]
[FONT=&quot]Eric[/FONT]

 

[MarcelBeug]

The way to go is to add a custom column "Custom" with formula = if [Location] = "DSC" and "HCWB" and [Entity] = 113 then 163 else [Entity]
Next remove the original "Entity" column and rename the new column to "Entity".

The syntax of Table.ReplaceValue is

Table.ReplaceValue(table as table, oldValue as any, newValue as any,replacer as function, columnsToSearch as {Text}) as table

An example:

= Table.ReplaceValue(TableName,"A","C",Replacer.ReplaceValue,{"Client", "Service"})

This replaces all A's with C's in table TableName in columns "Client" and "Service".
This example is with matching entire cell contents. For matching partial cell contents, you can use Replacer.ReplaceText instead of Replacer.ReolaceValue.

Otherwise you'd better just use the UI options and the code will be generated for you.

In general with column operations on the "Transform" tab, you can't just use values from other columns (it can be done if you combine values in a nested record, but that makes things complex).
Typically the options on the "Add Column" tab offer more flexibilty as these allow for other columns to be referenced.