Reference Previous Row Without Sorting & Index

[Anonymous]

Hi, I have a datasheet with a [depth] value, a [dollar] value, and a [category] value.

I would like to create a column that references the [dollar] value from the previous [depth] row, for each [category]. 

I have tried the: sort ascending, create an index method, but  the sort algorithm takes too long. The data set is millions of rows and is coming from a slow API. 

I have also tried a caluclated column with a variable for [category] and [depth], with the MAX, Calculate, selectedvalue, and filter function, but I recieve an error saying that I've run out of memory (this calculated column formula has worked with smaller datasets). 

I'd love to hear any reccomendations for an alternative solution that is more efificent for large datasets. 

Thank you in advance! 

[latimeria]

Hi @Anonymous ,

You can try adding the columns and shifting 1 row down .

You tranform the table into lists:

AllLIsts = Table.toColumns()
ShiftList = null & Table.RemoveLastN( , 1)[mycol]

and then you concatenate again.

Allists & mylist and Table.FromColumns

Video from Goodly here: https://www.youtube.com/watch?v=a7O2UlbTAvA

and here https://www.youtube.com/watch?v=IGF2-qfzDQs

I would be interested to know whether it's faster

[Daryl-Lynch-Bzy]

Hi @Anonymous - I would try using a Dataflow to store the results of the API.  This acts a physical storage of the data to separate the indexing from the API calls.  In the second dataflow you can focus on referencing the previous row.  If you have other steps to apply, you might try leaving them for a 3rd dataflow or for when you pull this into you Dataset.

[Anonymous]

@Daryl-Lynch-Bzy Thanks for the quick response, I sure appreciate it. 

That's an interesting idea, but I'm not sure how multiple data flows would differ from a single data flow. Don't I have to perform the same transformations in the second data flow, which references the previous row from the first dataflow? 

[Poohkrd]

Hi, it's differs a lot. And what algorithm you use after indexing your table? 

Try this one:

let
    Source = Table.FromColumns({{"a","b","c","d"}},{"col"}),
    col = List.Buffer({null}&Source[col]),
    AddedIndex = Table.AddIndexColumn(Source, "ind", 0, 1),
    TransformToPrevRow = Table.TransformColumns(AddedIndex, {{"ind", each col{_}}})
in
    TransformToPrevRow