Group By with Index and Calculated Column

[lanecarrier]

Hello All,

Within Power Query I have a table grouped by item ID (in this case API_NO) and can create a nested index column.

My goal is to add a calculated column within this step to calculate 'END_DATE' using the 'Index' and 'PRODUCTION_DAY' columns.  In short, 'PRODUCTION_DATE' is the start date  of the selected record and the end date of the previous record.

Here is my attempt at this:  

= Table.Group(#"Sorted Rows3", {"API_NO"}, {{"Count", each Table.AddIndexColumn(_, "Index", 0, 1), type table}, {"Counts", each Table.AddColumn(_, "END_DATE", each "Counts" [PRODUCTION_DAY] {[Index] + 1}), type table}})

This creates two lists of tables.

Any help is greatly appreciated,

Lane

[BA_Pete · Accepted solution]

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

[lanecarrier]

Wow, i got stuck down a rabbit hole for sure there.

Thank you!