Power Query subtract with replace

[u4us1923]

Hello

I'd like to subtract other figures from the total figure. then I'd like to replace the total value with the new value. how can I do this in Power Query?

[u4us1923]

thank you.. Its work! 🙏

[lbendlin · Accepted solution]

Design red flag. Do not repeat data in the same table.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WCskvScxR0oHThgYGSrE60UoBiUWZxUC+c2ZJJZAyBQv65Oel5OehiMYCAA==", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Column1 = _t, Column2 = _t, Column3 = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Column1", type text}, {"Column2", type text}, {"Column3", Int64.Type}}
  ), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Changed Type", 
    each [Column3], 
    each 
      if [Column2] = "Total" then
        [Column3] - List.Sum(List.Skip(#"Changed Type"[Column3], 1))
      else
        [Column3], 
    Replacer.ReplaceValue, 
    {"Column3"}
  )
in
  #"Replaced Value"

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". Once you examined the code, replace the Source step with your own source.