Convert the values ​​of a column imported from a table taking into account a conditional.

[DiegoBohorquez]

Hello, I am having a problem using Power Querry, I am just starting to use it but I have not been able to solve this problem.

I have a table that has several columns, including one with the title Type and another with the title Amount, I need that depending on what is written in Type, the Amount value becomes positive or negative but it gives me this error, some idea?

---------- Message ----------
[Expression.Error] We cannot apply field access to the type Number.

let

Source = Excel.CurrentWorkbook(){[Name = "Tbl_Tracking"]}[Content],

ConvertirANumero = Table.TransformColumns(

Source,

{

{"Amount", each try Number.FromText(Text.From(_)) otherwise null}

}

),

Operacion = Table.TransformColumns(

ConvertirANumero,

{

{"Amount", each

if [Type] = "Expenses" or [Type] = "Savings" then _ * -1

else _

}

}

)

in

Operacion

[wdx223_Daniel]

Operacion = Table.ReplaceValue(

ConvertirANumero,

each

if [Type] = "Expenses" or [Type] = "Savings" then   -1

else 1,

"",

(x,y,z)=>x*y,

{"Amount"}

)

[nandic · Accepted solution]

Try just adding new column, then removing old column and renaming new column to "Amount"?

Here is M script:
let
Source = Excel.CurrentWorkbook(){[Name = "Tbl_Tracking"]}[Content],
ConvertirANumero = Table.TransformColumns(
Source,
{
{"Amount", each try Number.FromText(Text.From(_)) otherwise null}
}
),
#"Changed Type" = Table.TransformColumnTypes(ConvertirANumero,{{"Type", type text}, {"Amount", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Type] = "Expenses" or [Type] = "Savings" then [Amount] * -1 else [Amount]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Amount"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Amount"}})
in
#"Renamed Columns"


Cheers,
Nemanja Andic

[DiegoBohorquez]

it worked perfectly, thanks men