Calculations in same row data and create new table

[Anildp]

Hi,

Need help with below situation

I have a table with the values as below 

this is the actual data 

Description	Value
Bucket 1	100
Bucket 2	300
Bucket 3	400
Bucket 5	200

I would like to create a Row in this table with below calculation  / Separate table with all buckets ordered in sequence format. 

Bucket 4 = (Bucket 1 -(Bucket 2 + Bucket 3))

can someone help me on how to achieve this?

Thanks

[v-juanli-msft · Accepted solution]

Hi @Anildp 

Finally i can get this table

Write this code in Advanced editor (Homw->Queries Editor)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcipNzk4tUTBU0lEyNDBQitWBCxkBhYxRhYyBQiaoQqYKQDEjkFgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Description = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Description", type text}, {"Value", Int64.Type}}),
    
    Source1 = #"Changed Type",
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([Description] = "Bucket 1")),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([Description] = "Bucket 2")),
    #"Filtered Rows3" = Table.SelectRows(#"Changed Type", each ([Description] = "Bucket 3")),
   
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "Custom1", each #"Filtered Rows3" [Value]),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Custom1", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted Values1",{{"Custom1", Int64.Type}}),
    #"Added Custom1.1" = Table.AddColumn(#"Changed Type1", "Custom1.1", each [Value]+[Custom1]),

    #"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Custom2", each #"Added Custom1.1"),
    #"Expanded Custom2" = Table.ExpandTableColumn(#"Added Custom2", "Custom2", {"Custom1.1"}, {"Custom.Custom1.1"}),
    #"Added Custom2.1" = Table.AddColumn(#"Expanded Custom2", "Custom2.1", each [Value]-[Custom.Custom1.1]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2.1",{"Value", "Custom.Custom1.1"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Custom2.1", "Value"}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns2","Bucket 1","Bucket 4",Replacer.ReplaceText,{"Description"}),
    
    #"Appended Query" = Table.Combine({#"Changed Type", #"Replaced Value2"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Description", Order.Ascending}})

in
    #"Sorted Rows"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anildp]

@v-juanli-msft  Thank you so much for the solution, it worked , thanks for helping me out.