Add a new calculated row in Power BI Desktop

[Elina_Mirin]

Hello,

I want to create a new calculated row in my table. I would like to do that in Power Query. Can anyone help, please?

I have a table that looks like this. On the last row I have the total of the above rows plus something else, which I want to categorize into "Other". So, I want to get rid of the last row, the "FULL" in Column B and I want to add a new calculated row that will be categorized into "Other" and which will be calculated as the difference between the "FULL" and the SUM between the other rows: 1135 - SUM(655+115+38+18+50+6+15)=238. Please see the screenshot below. 

Any advices on how can I achieve this result?

[Ahmedx · Accepted solution]

pls try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRcgRiM1NTpVgdmIgTEBsaIos4A7GxBZKAC0gJsoArEJsaIAm4gYxF4ruDdCCb6Rbq4wO2yBgqWpyC7hqwCIprwCLIrgELILsGLIDsGrAAkmvAfGTXQBRAXGNkYADUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}, {"Column C", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Count",
         (x) =>
          Table.ReplaceValue(
            Table.TransformColumns(x, {"Column B", (z) => if z = "FULL" then "Other" else z}), 
            each [Column C], 
            each 
              if [Column B] = "Other" then
                [Column C] - List.Sum(List.RemoveLastN(x[Column C], 1))
              else
                [Column C], 
            Replacer.ReplaceValue, 
            {"Column B", "Column C"}
          )
  }}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Column A", "Column B", "Column C"}, {"Column A", "Column B", "Column C"})
in
    #"Expanded Count"

[Ahmedx]

or try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVtJRcgRiM1NTpVgdmIgTEBsaIos4A7GxBZKAC0gJsoArEJsaIAm4gYxF4ruDdCCb6Rbq4wO2yBgqWpyC7hqwCIprwCLIrgELILsGLIDsGrAAkmvAfGTXQBRAXGNkYADUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t, #"Column C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}, {"Column C", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column A"}, {{"Count",
         (x) =>
          Table.ReplaceValue(
            x, 
            each [Column C], 
            each 
              if [Column B] = "FULL" then
                [Column C] - List.Sum(List.RemoveLastN(x[Column C], 1))
              else
                [Column C], 
            Replacer.ReplaceValue, 
            {"Column B", "Column C"}
          )
  }}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Count"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Other Columns", "Count", {"Column A", "Column B", "Column C"}, {"Column A", "Column B", "Column C"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Count","FULL","Other",Replacer.ReplaceText,{"Column B"})
in
    #"Replaced Value"

[ChiragGarg2512]

@Elina_Mirin , another row can not be created what can be done is making a measure which is the difference between Full and remaining values of column B.

[ChiragGarg2512]

@Elina_Mirin , I dont think calculated row can be added to a table using power query.

[Elina_Mirin]

Do you have another idea? Can it be done in DAX? I thought maybe it's easier in Power Query.