Indexing by date/time and another column

[Usates]

Here is my example:

I have Batch, Date, Time. I want to create an index column that will number each batch group by earliest time to latest time, and start over with the next batch number.

How do I do this?

[MudassirAli]

@Usates

Here you go

let
    Source = Excel.Workbook(File.Contents("C:\Users\Mudassir Ali\Desktop\Book1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Time", type time}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}, {"Time", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Batch"}, {{"Count", each _, type table [Batch=text, Date=nullable date, Time=nullable time]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Batch", "Date", "Time", "Index"}, {"Batch", "Date", "Time", "Index"})
in
    #"Expanded Custom" 

Book1.xlsx (8.6 KB) Indexing by date time and another column.xlsx (17.3 KB)

[Shumaila]

Hi Usates,
Check out this video, I guess it will solve your issue.

DAX Fridays! #165: Create an index based on two or more columns using DAX

Kind Regards

[Rajesh]

@Usates

Here is the solution with DAX. You can change the code based on your requirement.

Index.pbix (17.6 KB)