Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Subtract values from same column but based on value from another column in Query Editor

I want to get "Output Column" based on following condition in Power Query (M language): 

if column1 value is  "Peter" then "Output Column" would be value of (Peter - Fab- Kris) else value from column2

Column1Column2Output Column
Peter

1050

1050-95-75 =880
Dave110110
Ali5656
Fab9595
Kris7575

 

Thanks in advance!

General Comment
Need Help
Show and Tell
  • lbendlin's avatar
    lbendlin
    4 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtSS1S0lEyNDA1UIrViVZySSxLBfENIVzHnEwgz9QMzHFLTAJyLE3BHO+izGIgzxzIiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Output", each 
     if [Column1]="Peter" then [Column2]
                          -Table.SelectRows(#"Changed Type",each [Column1]="Fab"){0}[Column2]
                          -Table.SelectRows(#"Changed Type",each [Column1]="Kris"){0}[Column2] 
                          else [Column2])
in
    #"Added Custom"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtSS1S0lEyNDA1UIrViVZySSxLBfENIVzHnEwgz9QMzHFLTAJyLE3BHO+izGIgzxzIiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Output", each 
     if [Column1]="Peter" then [Column2]
                          -Table.SelectRows(#"Changed Type",each [Column1]="Fab"){0}[Column2]
                          -Table.SelectRows(#"Changed Type",each [Column1]="Kris"){0}[Column2] 
                          else [Column2])
in
    #"Added Custom"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".