Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Reference Previous Row Without Sorting & Index

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! 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Anonymous 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's avatar
        Poohkrd
        Advocate I
        3 years ago

        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