Create index using duplicate with numbers 1,2,3

[Saibal_78]

Hi All,

I have got a use case where I have some duplicate values in the dataframe with start and end date. Now I want to create an index column as shown below. I want to create the index column considering the start date, latest date being considered as first index.

	mmddyyyy
ID	Start Date	End Date	Index
ABC	9/12/2023	9/30/2023	1
ABC	7/6/2023	9/11/2023	2
ABC	5/18/2023	7/5/2023	3
BDE	2/11/2022	4/11/2022	1
BDE	12/11/2021	2/10/2022	2
DRB	3/9/2023	5/17/2023	1
DRB	1/10/2023	3/8/2023	2
DRB	11/5/2022	1/9/2023	3
DRB	10/1/2022	11/4/2022	4

I am able to achieve the purpose using below power query when start dates are not taken into consideration, as soon as I include start date the solution does not work.

#"Grouped Rows" = Table.Group(Source, {"ID"}, {{"Data", each _, type table [Names=nullable text, Index=number]}}),
#"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Subtable", each Table.AddIndexColumn ([Data], "Index2", 1, 1, Int64.Type )),
#"Removed Other Columns1" = Table.SelectColumns(#"Added Custom",{"Subtable", "Index"}),
#"Expanded Subtable" = Table.ExpandTableColumn(#"Removed Other Columns1", "Subtable", {"ID", "Index2"}, {"ID", "Index2"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Subtable",{"Index"}),

#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Index2", "Index"}})

Any help would be highly appreciated.

[lbendlin · Accepted solution]

You were close.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY5LDsAgCAXv4toEQa26rLUX6NZ4/2uU+CNpdy/MAK9WdeZLaZUACciQ7dmakZtePMAhGPGLPWBcPIAXnMvNI5orxNntLByXgEM2IpQn88xCWuf5VZD7A+NcsV2NP4yjEnU1/bCB3Y5VN5+3Fw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Start Date", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, { {"Index", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
    #"Expanded Index" = Table.ExpandTableColumn(#"Grouped Rows", "Index", {"Start Date", "End Date", "Index"}, {"Start Date", "End Date", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Start Date", type date}, {"End Date", type date}, {"Index", Int64.Type}})
in
    #"Changed Type1"