Table.TransformColumns by criteria

[mechanix85]

Hello! How can I convert a column by a condition?

Something like this

if type = №2 and type2 = true then List.Product({_, -1}) else List.Product({_, 1})

???

[eziola]

Dale! The Table.ReplaceValue solution works great! I was able to add a custom function to this solution so I can reference other columns like this:

=Table.ReplaceValue(

table,

each [ColumnToModify],

each fx_CustomFunction( [ColumnAsArgument1], [ColumnAsArgument2] ),

Replacer.ReplaceValue,

{"ColumnToModify"}

)

Question for the thread:

Does anyone know if there is a way to have Table.TransformColumns use other columns in the table as arguments to the transformation fuction? (like this...)

Table.TransformColumns(

table as table,

{{column_name, fx_CustomFunction ( [ColumnAsArgument1], [ColumnAsArgument2] ), DataType}}

)

Thanks for any insight on this!

[Anonymous]

Hi, 

  Dale's solution does work for me. However I was wondering if there is a way to transform multiple columns in one step? Right now I have to repeat on every column I wish to transform.

  Thanks.

Carl

[v-jiascu-msft]

Hi @mechanix85,

You could try this formula.

= if [type] = "No2" and [type2] = true then [value] * -1 else [value] * 1

Notes: M is case sensitive.

Best Regards!

Dale

[mechanix85]

@v-jiascu-msft, Yes, i can do this, but I do not want to add new column... Table.TransformColumns can do it without new column, but I can not understand how to add a condition

[v-jiascu-msft · Accepted solution]

Hi @mechanix85,

You could try this. It worked in my test.

#"Replaced Value" = 
          Table.ReplaceValue(#"Changed Type1",
          each [value],
          each if [type] = "No2" and [type2] = true then [value] * -1 else [value] * 1,
          Replacer.ReplaceValue,{"value"}),

Best Regards!

Dale