I need an aggregated values like below

[Anonymous]

I have Id and Price coulmn. But need realprice column in same table

[tackytechtom · Accepted solution]

Hi @Anonymous ,

Here a solution in Power Query.

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough) :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrVwcU0AjFN0ZnGQKYRkBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Price", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Id"}, {{"Grouping", each _, type table [Id=nullable number, Price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Id", "Price", "Index"}, {"Id", "Price", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Index] > 1)),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "RealPrice", each 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id", "Price"}, {{"RealPrice", each List.Sum([Price]), type nullable number}}),
    #"Appended Query" = Table.Combine({#"Grouped Rows", #"Removed Columns"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Id", Order.Ascending}})
in
    #"Sorted Rows"

Let me know if this one works for you 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can follow the steps to get it, please find the details in the attachment.

1. Add Index column in Power Query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrVwcU0AjFN0ZnGQKYRkBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Price", Int64.Type}}),
    #"Added Index" =  Table.Group(#"Changed Type", {"Id"}, {{"Index", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Added Index", "Index", {"Price", "Index"}, {"Price", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Price", Int64.Type}, {"Index", Int64.Type}, {"Id", Int64.Type}})
in
    #"Changed Type1"

2. Create a calculated column as below

RealPrice = 
        IF(
            'Table'[Index] = 1,
            CALCULATE(
                SUM('Table'[Price]),
                FILTER(
                    'Table',
                   'Table'[Id]=EARLIER('Table'[Id])
                )
            ),
            0
        )

Best Regards

[tackytechtom · Accepted solution]

Hi @Anonymous ,

Here a solution in Power Query.

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough) :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0UIrVwcU0AjFN0ZnGQKYRkBkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Price", Int64.Type}}),
    #"Grouped Rows1" = Table.Group(#"Changed Type", {"Id"}, {{"Grouping", each _, type table [Id=nullable number, Price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Id", "Price", "Index"}, {"Id", "Price", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Index] > 1)),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows", "RealPrice", each 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Id", "Price"}, {{"RealPrice", each List.Sum([Price]), type nullable number}}),
    #"Appended Query" = Table.Combine({#"Grouped Rows", #"Removed Columns"}),
    #"Sorted Rows" = Table.Sort(#"Appended Query",{{"Id", Order.Ascending}})
in
    #"Sorted Rows"

Let me know if this one works for you 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/