Issue with Self join in Dataflow Gen 2

Cool. Thanks for the update _Smrithi .

It does seem strange to be getting this error on only 5M rows. Up the the point that you add the indices for self-join, is the query maintaining folding i.e. is 'View Data Source Query' lit up and selectable when you right-click the query step before adding the first index? The more of the query that you can get the source to process (assuming an SQL source) before doing your transformation should help with memory load.

There are other ways to achieve the same result which should avoid the huge amount of table scanning that a merge does. Here's one method that you can try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIw1AciIwMjYyjHDMKJ1UHImyHLW6LKGxroAxFU3thQ39AIKh8LAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Valid From" = _t, #"Valid To" = _t]),
    Cols = Table.ToColumns(Source),
    addPrevValidTo = Table.FromColumns(Cols & {{null} & List.RemoveLastN(List.Last(Cols),1)}, Table.ColumnNames(Source) & {"PrevValidTo"})
in
    addPrevValidTo

The example code above transforms this:

...to this:

Pete