Help with DAX power BI

---

@Syndicate_Admin wrote:

Hello @Syndicate_Admin , I hope you're okay! Can we tell if it worked for you or are you still struggling? Tell us: your feedback can help others in the same situation a lot.


Hello, I have modified and applied the code in M language that a colleague gave me which is the following:

Let me
// 1. Reference to your original table
Base = Arrests,

// 2. Create combined Date and Time columns
AddStartDateTime = Table.AddColumn(
Base,
"StartDateTime",
each DateTime.From([#"Start Date"]) + Duration.From([#"Start Time"]),
type datetime
),
AddEndDateTime = Table.AddColumn(
AddStartDateTime,
"EndDateTime",
each DateTime.From([#"Start Date"]) + Duration.From([#"End Time"]),
type datetime
),

// 3. Sort Data (Vital for Previous Row Logic)
SortedRows = Table.Sort(
AddEndDateTime,
{
{"Start Date", Order.Ascending},
{"Line", Order.Ascending},
{"StartDateTime", Order.Ascending}
}
),

// 4. Add Table of Contents to compare with the previous row
AddIndex = Table.AddIndexColumn(SortedRows, "Index", 0, 1, Int64.Type),

// 5. Create the Group ID using a custom function to avoid slow "Join"
This part identifies whether the current row belongs to the previous group or is a new one
AddGroupFlag = Table.AddColumn(AddIndex, "NewGroupFlag", each
Let me
CurrentRow = AddIndex{[Index]},
PreviousRow = if [Index] > 0 then AddIndex{[Index]-1} else null,
IsNewGroup =
if PreviousRow = null then 1
else if CurrentRow[Line] <> PreviousRow[Line] then 1
else if CurrentRow[#"Start Date"] <> PreviousRow[#"Start Date"] then 1
else if CurrentRow[#"Level 2"] <> PreviousRow[#"Level 2"] then 1
else if Duration.TotalSeconds(CurrentRow[StartDateTime] - PreviousRow[EndDateTime]) > 30 then 1
else 0
in
IsNewGroup, Int64.Type
),

// 6. Create a Running Total ID for Flags
This groups consecutive rows under a single ID
AddGroupID = Table.AddColumn(AddGroupFlag, "GroupID", each List.Sum(List.FirstN(AddGroupFlag[NewGroupFlag], [Index] + 1)), Int64.Type),

// 7. Group by GroupID
GroupedStops = Table.Group(
AddGroupID,
{"GroupID", "Start Date", "Line", "Level 2"},
{
{"Start Time", each List.Min([#"Start Time"]), type time},
{"End Time", each List.Max([#"End Time"]), type time},
{"Time in Minutes", each List.Sum([#"Time in Minutes"]), type number}
}
),

Final cleanup: Remove the group ID column if you don't need it
RemovedGroupID = Table.RemoveColumns(GroupedStops,{"GroupID"})
in
RemovedGroupID

However, I have not been successful, I do not know if I translated the code correctly so that it is used correctly. I remain attentive

---