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 [...
Need Help
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