Forum Discussion
Group By with Index and Calculated Column
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
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 remOthColsSummary:
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
2 Replies
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 remOthColsSummary:
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
Wow, i got stuck down a rabbit hole for sure there.
Thank you!
