@kellysolutions It’s not possible to provide a solution without looking at the data. You can refer to the following video in case you can’t share your original data.
While I’m probably just daring @Melissa to prove me wrong here, I think in the case of retrieving previous row values, DAX may be a better bet here in terms of size and performance than Power Query. @Greg has put together a terrific DAX Patterns section on Previous Row Value that I would recommend you review and try some of the variations he delves into:
Hi @kellysolutions, we hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Okay so in my opinion for small tables performance of a Prev Row lookup with a Custom Column works just fine but for larger tables there are definitely some performance considerations.
As an alternative could you try the following and see if that works better for you:
First add an Index from 0
Next add an Index form 1
Perform a self Merge on the two Index columns to get the Prev Row value
I had come across the two index method, but didn’t understand it at first. I took another look at it this morning and it did my job perfectly. Thank you.
@BrianJ I decided to do this in Power Query rather than DAX as I still had some manipulation to do .