Group By with Index and Calculated Column

Hi lanecarrier ,

I don't think you need Group By for this - you can just merge the table on itelf with two indexes:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/LDYAwDAPQXXJGip3Q3yyo+69BQoXogeuTnc91Cc3lEFBhakCVebxopuiJfUf/wawzkHzwLDWQTVG+5MLYgpE4doz6mXXb0LrCE8uDrY9AhzIXoW3ISLZv5sI4nvkRXea8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [API_NO = _t, PRODUCTION_DAY = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"API_NO", Int64.Type}, {"PRODUCTION_DAY", type date}}),
    sortAPI_DAY = Table.Sort(chgTypes,{{"API_NO", Order.Ascending}, {"PRODUCTION_DAY", Order.Ascending}}),
    addIndex0 = Table.AddIndexColumn(sortAPI_DAY, "Index0", 0, 1, Int64.Type),
    addIndex1 = Table.AddIndexColumn(addIndex0, "Index1", 1, 1, Int64.Type),
    mergeOnSelf = Table.NestedJoin(addIndex1, {"API_NO", "Index1"}, addIndex1, {"API_NO", "Index0"}, "addIndex1", JoinKind.LeftOuter),
    expandPROD_DAY = Table.ExpandTableColumn(mergeOnSelf, "addIndex1", {"PRODUCTION_DAY"}, {"PRODUCTION_DAY.1"}),
    remOthCols = Table.SelectColumns(expandPROD_DAY,{"API_NO", "PRODUCTION_DAY", "PRODUCTION_DAY.1"})
in
    remOthCols

Summary:

sortAPI_DAY = Sort original table in order of API Asc, then DAY Asc.

addIndex0 / addIndex1 = Create two indox columns, one starting from zero, the other from one.

mergeOnSelf = Merge the table on itself using [API_NO]&[Index1] = [API_NO]&[INDEX0]

expandPROD_DAY = Expand the [PRODUCTION_DAY] column from the nested table to get the next row value.

Example output:

Pete