Latest Enterprise DNA Initiatives

Previous Row - getting a value from a previous row

Power BI
#1

  1. I have table with 10 columns and 16,000 rows. Approximately 1MB.

  2. It is a Lookup table that needs quite a bit of manipulation before it is ready for use. (Its is a multi-record type table…)

  3. I came across the idea of creating an index column and then adding a custom column that retrieves the previous value.

  4. When I create and apply the final column, to retrieve the previous value, the size of the model explodes - 6.5 GB and counting.

  5. Is there a more efficient method to achieve my goal or is there something I am missing

Thank you,

paul

#2

@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.

Thanks.

#3

@kellysolutions,

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:

I hope this is helpful.

– Brian

#4

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!

#5

Hi @kellysolutions,

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:

  1. First add an Index from 0
  2. Next add an Index form 1
  3. Perform a self Merge on the two Index columns to get the Prev Row value

I hope this is helpful.

1 Like
#6

Thank you to @Melissa @BrianJ and @MudassirAli for replying to my query.

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 .

Thank you all again.,

2 Likes