Forum Discussion

themistoklis's avatar
themistoklis
Community Champion
5 years ago
Solved

Subtract Values in consecutive rows

Hello All,   Im working on a task where I need to subtract values from consecutive rows. More specifically i have a spreadsheet (see attached) where Culumn C contains cumulative values for cate...
General Comment
Need Help
  • edhans's avatar
    edhans
    5 years ago

    themistoklis - one more solution:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATETIME", type datetime}, {"CATEGORY", type text}, {"CUMULATIVE VIEWS", Int64.Type}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"CATEGORY", Order.Ascending}, {"DATETIME", Order.Ascending}})),
    listCategory = List.Buffer(#"Sorted Rows"[CATEGORY]),
    listCumulative = List.Buffer(#"Sorted Rows"[CUMULATIVE VIEWS]),
    listCount = List.Count(listCategory),
    listCumulativeDifference =
        List.Skip(
            List.Generate(
                ()=> [varCategory = listCategory{0}, varDiff = 0, Counter = 0],
                each [Counter] <= listCount,
                each 
                    try
                        if [Counter] = 0 
                            then [varDiff = listCumulative{[Counter]}, Counter = [Counter]+ 1]
                        else if listCategory{[Counter]} = listCategory{[Counter] - 1}
                            then [varDiff = listCumulative{[Counter]} - listCumulative{[Counter] -1}, Counter = [Counter]+ 1]
                            else [varDiff = listCumulative{[Counter]}, Counter = [Counter]+ 1]
                    otherwise [Counter = [Counter] + 1],
                each [varDiff]
            ),
            1
        ),
    CombinedColumns = 
        Table.ToColumns(#"Sorted Rows") & {listCumulativeDifference},
    BackToTable = 
        Table.FromColumns(
            CombinedColumns,
            Table.ColumnNames(#"Sorted Rows") & {"Difference"}
        )
in
    BackToTable

    This will do 100,000 rows as fast as Excel can think about loading the .NET framework to process it. I suspect 1M rows would be equally fast.

     

    this is in the original file link above so you can get the solution there. it has 100K rows for testing now.

Jimmy801's avatar
Jimmy801
Community Champion
5 years ago

Hello themistoklis 

 

i tried a similar approach that Anonymous ... also because I didn't check out all answers allready given 🙂

I used the group-function and then added a new column with a shifted version of your cumulative column (all the code of the Table.TransformColumns could also be integrated into the group-function). Then subtracted both of them. Here a standalone-code 🙂

I would be currious how this code is performing

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZQxbsMwEAS/Yqg2DO5JFEl3UZ5huHBhpAwQpPHvbbELZwOXBAZ3PM4tL5dJ7SSdIqkdks4q03H6vP3ev75/HoeP1yHW6Xr8i4XDKrDZYHMCtjgsA8sOa8BWgy0BrDisAKsGyzOwZrAVkyoZrBBzFgpGkLNQ8G5yFgreTc5CZVNnobKas9A4qbPQOIKzoMTLOQ1KqBfOgwSt4UQoMEY4Ewr2dSo0M19j363f720MO8ZVH7t2jPkaF2DHgk3HBejY8jaGm8/+uAAd4wjjAnSMH87of7OhRgx3LHPrnIXMas6Cyb6zsOLdEMOOsamzUFjNWTBfhLNQOYKzUHk3Z4HZRwg7BqfI4PZPpm0YxLY2DfueX58=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATETIME = _t, CATEGORY = _t, #"CUMULATIVE VIEWS" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATETIME", type datetime}, {"CATEGORY", type text}, {"CUMULATIVE VIEWS", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"DATETIME", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"CATEGORY"}, {{"AllRows", each _, type table [DATETIME=datetime, CATEGORY=text, CUMULATIVE VIEWS=number]}}),
    AddHourlyViews = Table.TransformColumns
    (
        #"Grouped Rows",
        {
            {
                "AllRows", 
                (tbl)=> Table.AddColumn
                (
                    Table.FromColumns
                    (
                        Table.ToColumns(tbl) & {{0}&(List.RemoveLastN(tbl[#"CUMULATIVE VIEWS"]))},
                        Table.ColumnNames(tbl)&{"Shifted"}
                    ),
                    "Hourly Views",
                    (add)=> add[#"CUMULATIVE VIEWS"]-add[Shifted]
                )
            }
        }
    ),
    #"Expanded AllRows" = Table.ExpandTableColumn(AddHourlyViews, "AllRows", {"DATETIME", "CUMULATIVE VIEWS", "Hourly Views"}, {"DATETIME", "CUMULATIVE VIEWS", "Hourly Views"})
in
    #"Expanded AllRows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy