Inverse values based on condition without adding column

[Syndicate_Admin]

I am trying to inverse the values in ACTUAL, BUDGET, FORECAST columns based on a condition in Account column where if the value = "Discount".

Below is the formula I have written.

ConvertDiscount = Table.TransformColumns(ExpandAccountHierarchy, {
{"ACTUAL", each if [Account] = "Discount" then _ *-1 else _, type number},
{"BUDGET", each if [Account] = "Discount" then _ *-1 else _, type number},
{"FORECAST ", each if [Account] = "Discount" then _ *-1 else _, type number}},

But, it is giving me an error "Expression.Error: We cannot apply field access to the type Null. Details: Value= Key=Account". I have verified and ensured that the BUD, FCST & ACT columns has the type as number and Account as Text and all these columns doesn't have Null values. Can you please help me if there is something I am missing in this formula

[Syndicate_Admin]

This is a great function, but since the volume of the data I am working with is huge it would not be ideal. Anyways now I understand that in Transform.Columns we cannot refer to 2 columns, so the ideal and the best solution would be to have separate formulas for each Column. Appreciate your help

[AlienSx · Accepted solution]

Hi, deepak_shashi  

let
    Source = your_table,
    reversal = 
        Table.FromRecords(
            Table.TransformRows(
                Source, 
                (x) => 
                    [mult = (z) => if x[Account] = "Discount" then -z else z,
                    result = 
                        Record.TransformFields(
                            x, 
                            {{"ACTUAL", mult},
                            {"BUDGET", mult},
                            {"FORECAST", mult}}
                        )][result]
            )
        )
in
    reversal

[ImkeF]

Hi @Syndicate_Admin ,
you cannot reference a different column from withing the Table.TransformColumns function.
Instead, you have to use Table.ReplaceValue  :
Table.TransformColumns - alternative in PowerBI and PowerQuery in Excel (thebiccountant.com)

And if you want to do it for multiple columns at once, check out this article:
Transforming multiple columns at once with reference to existing column in Power Query (thebiccounta...