Forum Discussion

MindIsBlank's avatar
MindIsBlank
Frequent Visitor
5 years ago
Solved

Transform column based on value of another column

I am trying to add the sign to a transaction amount column. Values need to be positive for amounts increasing balance and negative for values decreasing balance. Currently, the transaction amount column does not have a sign. The sign is determined by the value in another column, Type.

 

I tried to do this using Table.TransformColumns but I'm getting a 'We cannot apply field access to the type Number' error.

 

 

Table.TransformColumns(#"Added Custom",{{"amount", each let multipler = if [Type]="Withdrawal" then -1 else 1 in Value.Multiply(_,multipler)}})

 

 

 

I also tried to apply the transform on rows instead using this code (from here) :

 

 

fnAddSign(#"Added Custom", {{"amount", each let multipler = if [Type]="Withdrawal" then -1 else 1 in Value.Multiply(_,multipler)}})

(table as table, transforms as list) =>
let
    Output = Table.FromRecords(
        Table.TransformRows(
            table,
            (r) =>
            Record.TransformFields(
                r,
                transforms
            )
        ),
        Value.Type(table)
    )
in
    Output

 

 

 

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, MindIsBlank 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may try the following transformations in 'Advanced Editor'.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs8syUgpSixX0lEyVIrViVZySS3IL84sAfKNwHwkBcZoCkyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Value", Int64.Type}}),
    Custom1 = (table as table, transforms as list) =>
let
    Output = Table.FromRecords(
        Table.TransformRows(
            table,
            (r) =>
            Record.TransformFields(
                r,
                {"Value",each if r[Type]="Withdraw" then -1*r[Value] else r[Value]}
            )
        ),
        Value.Type(table)
    )
in
    Output,
    Custom2 = Custom1(#"Changed Type",#"Changed Type"[Value])
in
    Custom2

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps,then consider Accepting it as the solution to help other members find it faster.

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    You can add a custom column like this

     

    = if [Type]="Withdrawal" then -1 * [amount] else [amount]

     

    Regards,

    Pat

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, MindIsBlank 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may try the following transformations in 'Advanced Editor'.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs8syUgpSixX0lEyVIrViVZySS3IL84sAfKNwHwkBcZoCkyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Value", Int64.Type}}),
    Custom1 = (table as table, transforms as list) =>
let
    Output = Table.FromRecords(
        Table.TransformRows(
            table,
            (r) =>
            Record.TransformFields(
                r,
                {"Value",each if r[Type]="Withdraw" then -1*r[Value] else r[Value]}
            )
        ),
        Value.Type(table)
    )
in
    Output,
    Custom2 = Custom1(#"Changed Type",#"Changed Type"[Value])
in
    Custom2

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps,then consider Accepting it as the solution to help other members find it faster.