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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

 

 

decumul:

 

let
decum=(tab)=>
let
    listCumulative = List.Buffer(tab[CUMULATIVE VIEWS]),
    listCount=List.Count(listCumulative),
    names=Table.ColumnNames(tab)&{"shift"},
    shift= List.Generate(
                ()=> [varDiff = listCumulative{0}, Counter = 0],
                each [Counter] < listCount,
                each [varDiff=listCumulative{Counter}-listCumulative{[Counter]},Counter=[Counter]+1],
                each [varDiff]
            ),
    ttc=Table.ToColumns(tab)
in 
   Table.FromColumns(ttc&{shift},names)
    

in decum

 

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZRBagMxDEWvEmYdiKWxR3Z2cY8RssgidFko3fT2HWtwodUXaGNIeBjP/9K73xdqF6ILJ2qnRFeSK/NyXt6eX6/3j8/v023/wdtxPM5/cXbwOg5Dr5heE6SzQ5f9KIYuSpOh235kQ2/47jz+WQ0tDi3wJRXTZd0Pm2DD9DYykf80JUzLoCkZ3GlTGAVOTplSUCjklCkwcHLKrDBwKg7dYChOmU1DMbRTZoNjRRWPFSWGT3HapAQz5OTcTmNWqsHJwRkOCzt9EsNPZadQWjd9kplcMFz9ICEOXqM4o5aQKpSGOSJVDFqjsisHVKF0hneD6epTWvZuMF1Kw41DqlC6RlXRp4ZaRBWDLgWNFjKF0hI1RZ/SCplC6Rw1hdISNcWgJUdN0afhQqYYdE1RUygtUVH06ayQJ5SuUU30XwmFNHHsMfxOpIljj+feP34A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATETIME = _t, CATEGORY = _t, #"CUMULATIVE VIEWS" = _t, #"HOURLY VIEWS (POWER QUERY CALCULATION)" = _t]),
    #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"DATETIME", type datetime}, {"CATEGORY", type text}, {"CUMULATIVE VIEWS", Int64.Type}, {"HOURLY VIEWS (POWER QUERY CALCULATION)", Int64.Type}}),
    #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"CATEGORY"}, {"all", each decumul(_)}),
    #"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"DATETIME", "CUMULATIVE VIEWS", "HOURLY VIEWS (POWER QUERY CALCULATION)", "shift"}, {"DATETIME", "CUMULATIVE VIEWS", "HOURLY VIEWS (POWER QUERY CALCULATION)", "shift"})
in
    #"Tabella all espansa"