Calculations in same row data and create new table

Question

Hi,&nbsp;Need help with below situation&nbsp;I have a table with the values as below&nbsp;&nbsp;this is the actual data&nbsp;&nbsp;DescriptionValueBucket 1100Bucket 2300Bucket 3400Bucket 5&nbsp;200&nbsp;I would like to create a Row in this table with below calculation&nbsp; / Separate table with all buckets ordered in sequence format.&nbsp;&nbsp;Bucket 4 = (Bucket 1 -(Bucket 2 + Bucket 3))&nbsp;can someone help me on how to achieve this?&nbsp;Thanks

v-juanli-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;
Finally i can get this table

Write this code in Advanced editor (Homw-&gt;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 RegardsMaggie
&nbsp;
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Calculations in same row data and create new table

2 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Create calculated table from two tables

Direct Lake Model - Calculated Column/Tables

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