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.

edhans's avatar
edhans
Community Champion
5 years ago

See if this works for you themistoklis 

I did 2 things:

  1. sorted the data by category and time first (ensuring the right sort - the used Table.Buffer to prevent a change)
  2. If the category changes, it resets the count.
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}})),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    PreviousRow = 
        Table.AddColumn(
            #"Added Index",
            "Difference",
            each
                let
                    varCurrentRow = [Index],
                    varCurrentCategory = [CATEGORY]
                in
                if varCurrentRow = 0 then [CUMULATIVE VIEWS]
                else if varCurrentCategory <> #"Added Index"[CATEGORY]{varCurrentRow - 1} then [CUMULATIVE VIEWS]
                else [CUMULATIVE VIEWS] - #"Added Index"[CUMULATIVE VIEWS]{varCurrentRow - 1},
                Int64.Type
            )
in
    PreviousRow

 

Here is your file back. You can of course delete the index column if you don't want it at this point.