Group rows with same dates and consecutive dates

Hi JoMont, another solution:

Output

You can delete CHangedTypeEU and ChangedTypeUS steps if you do not need them.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZGxCoMwEIZfJWQWTS4x6twW6SDt0kkcUj1KQCJoKPj2jVaKdrBLt/9yfB//kbKknAa0OB0Zl8KnHC32uiUFNqY2Fv0TjyPOImAw7SFdDTxMFa2CksIvB2QrTEWMT1lOCjYLxCKQWeLT5T44dL2pB6JtQ/LRaqy7tnuMfpkutHhXY/IzsDCeZfIPMrG44sWlIPPpqv1JepbtNpEzqzbsbg+uVjhAxJIvV7Jxna1DO5gnkoPukdyscRMnNtz6o4BW1Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Medical Registration Number" = _t, #"Discipline Type" = _t, #"Start Date" = _t, #"End Date" = _t, #"FTE equivalent" = _t]),
    ChangedTypeEU = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Start Date", type date}, {"End Date", type date}}),
    ChangedTypeUS = Table.TransformColumnTypes(ChangedTypeEU,{{"FTE equivalent", type number}}, "en-US"),
    GroupedRows = Value.ReplaceType(Table.Combine(Table.Group(ChangedTypeUS, {"Medical Registration Number", "Discipline Type"}, {{"All", each _, type table}, {"T", each Table.FromRecords({_{0} & [Start Date = _{0}[Start Date], End Date = List.Last([End Date]), FTE equivalent = List.Sum([FTE equivalent])]}) , type table}})[T]), Value.Type(Table.FirstN(ChangedTypeUS, 0))),
    ReplacedIndex = Table.ReorderColumns(Table.AddIndexColumn(Table.RemoveColumns(GroupedRows,{"Index"}), "Index", 1, 1, Int64.Type), Table.ColumnNames(GroupedRows))
in
    ReplacedIndex

Hi JoMont ,

I understand you're trying to group rows in two scenarios:

1. When the same medical registration number and discipline have overlapping date ranges (e.g. same placement split across rows).
2. When the same person and discipline have continuous dates (i.e., the next placement starts the day after the last one ends).

Here's a working solution in Power Query that handles both cases reliably  even on large datasets:

Steps in Power Query.

1. Load your table into Power Query.
2. Make sure Start Date and End Date are of type data.
3. Paste the following code into the Advanced Editor:

let
    Source = YourTableNameHere,  // replace with actual step/table
    ChangedTypes = Table.TransformColumnTypes(Source, {{"Start Date", type date},{"End Date", type date},{"FTE equivalent", type number}}),
    Sorted = Table.Sort(ChangedTypes, {{"Medical Registration Number", Order.Ascending},{"Discipline Type", Order.Ascending},{"Start Date", Order.Ascending}}),
    AddIndex = Table.AddIndexColumn(Sorted, "IndexHelper", 0, 1),
    AddPrevious = Table.AddColumn(AddIndex, "Previous", each try AddIndex{[IndexHelper]-1} otherwise null),
    FlagNewGroup = Table.AddColumn(AddPrevious, "NewGroup", each
    let
    curr = [Medical Registration Number],
    prev = [Previous][Medical Registration Number],
    sameDisc = [Discipline Type] = [Previous][Discipline Type],
    isContiguous = [Start Date] <= Date.AddDays([Previous][End Date], 1),
    samePerson = curr = prev
    in
if samePerson and sameDisc and isContiguous then 0 else 1),
RemovePrev = Table.RemoveColumns(FlagNewGroup, {"Previous"}),
AddRunningGroup = Table.AddIndexColumn(RemovePrev, "RowIndex", 0, 1),
AddGroupID = Table.AddColumn(AddRunningGroup, "GroupID", each List.Sum(List.FirstN(RemovePrev[NewGroup], [RowIndex]+1))),
Grouped = Table.Group(AddGroupID,{"Medical Registration Number", "Discipline Type", "GroupID"}, {{"Start Date", each List.Min([Start Date]), type date},
{"End Date", each List.Max([End Date]), type date},{"FTE equivalent", each List.Sum([FTE equivalent]), type number}}),
AddFinalIndex = Table.AddIndexColumn(Grouped, "Index", 1, 1)
    in
        AddFinalIndex

After all above steps follow bellow steps.

• Merges rows for the same person + same discipline where dates either overlap or are continuous.
• Sums the FTE equivalent.
• Keeps only one record per logical placement block.

Let me know if you'd like to retain other columns happy to help adapt further.

Regards,
Akhil.

just in case you have overlapping periods...

let
    fx_same_group = (x, y, max_date) => x{1} = y{1} and x{2} = y{2} and (x{3} <= max_date  + #duration(1, 0, 0, 0)),
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    sort = Table.Sort(Source, {"Medical Registration Number","Discipline Type", "Start Date"}),
    rows = List.Buffer(Table.ToList(sort, (x) => x)), 
    gen = List.Generate(
        () => [i = 0, group_num = 1, dmax = rows{0}{4}],
        (x) => x[i] < List.Count(rows),
        (x) => [
            i = x[i] + 1,
            group_num = x[group_num] + Number.From(not fx_same_group(rows{i}, rows{x[i]}, x[dmax])),
            dmax = if group_num = x[group_num] then List.Max({x[dmax], rows{i}{4}}) else rows{i}{4}
        ],
        (x) => rows{x[i]} & {x[group_num]}
    ),
    tbl = Table.FromList(gen, (x) => x, Table.ColumnNames(Source) & {"group"}),
    result = Table.Group(
        tbl, 
        {"group", "Medical Registration Number","Discipline Type"},
        {
            {"Start Date", (x) => List.Min(x[Start Date])},
            {"End Date", (x) => List.Max(x[End Date])},
            {"FTE equivalent", (x) => List.Sum(x[FTE equivalent])}
        }
    )
in
    result

JoMont You can achieve the result bu using Group by Rows as well. Refer below code

et
    Source = Table,
    Result = let
varGroupedRows = 
Table.Group(Source, {"Medical Registration Number", "Discipline Type"}, {{"Start Date", each List.Min([Start Date]), type nullable date}, {"End Date", each List.Max([End Date]), type nullable date}, {"FTE Equivalent", each List.Sum([FTE equivalent]), type nullable number}}),
varAddIndex = Table.AddIndexColumn(varGroupedRows,"Index",1,1),
varResult = Table.ReorderColumns(varAddIndex,{"Index"} & Table.ColumnNames(varGroupedRows))
in
varResult
in
    Result

Below screenshot

Regards,

sanalytics

can't delete this reply

Hi JoMont,
Hope you are doing well!

I have used Grouping and Transformation Technique to handle the issue. Find the below M-Code.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZGxasMwEIZfRWiOwt1JtuW5LSFDaJdOJoPqiCAICtimkLePpaiplILbodv9Et/H/VLXceQrvnt5BlRynjbW28Gc2M4eXO+8nY+wEgiCgMI96SzgWtd8v+o4/eagNsOgFoAhqOCAaJDJoNpmnl4/xslOg+tHZvyBbS7e2P58Oh8vAdcJl7flQN0DrKtoU/9hk0lWJVlN7Ty9mbmVibblXVSE6wJe3ATrDCcS0Dy4msK19ZP1o/u07MkMlr17NwVOFlz+WRQl+ksi4WcbFED3DfKQfqkt4MU2ocA33giQD20Q/i5DnfNt/k43Fy60IshgCSW8vwI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, #"Medical Registration Number" = _t, #"Discipline Type" = _t, #"Start Date" = _t, #"End Date" = _t, #"FTE equivalent" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Medical Registration Number", type text}, {"Discipline Type", type text}, {"Start Date", type date}, {"End Date", type date}, {"FTE equivalent", type number}}),

// Sorting by Medical Registration Number and Start Date is important here.
// Without applying sorting logic, sometimes gives you different result.

Sort_Logic = Table.Sort(#"Changed Type",{{"Medical Registration Number", Order.Ascending}, {"Start Date", Order.Ascending}}),

Group_Logic_1 = Table.Group(Sort_Logic, {"Medical Registration Number", "Discipline Type"}, {{"AllRows", (x) => x}}, GroupKind.Local),

//Assuming your data has continuous date period where a medical number is doing two different placements in the same discipline.
// If Date range is not continous, sometimes gives you different result.

Transform_Logic = Table.TransformColumns(Group_Logic_1, {{"AllRows", (x) => Table.TransformColumns(x, {{"Start Date", (y) => List.Min(x[Start Date])},{"End Date", (y) => List.Max(x[End Date])}})}}),

Combine_Logic = Table.Combine(Transform_Logic[AllRows]),

Group_Logic_2 = Table.Group(Combine_Logic, {"Medical Registration Number", "Discipline Type", "Start Date", "End Date"}, {{"FTE equivalent", each List.Sum([FTE equivalent]), type nullable number}}),
Output = Table.TransformColumnTypes(Group_Logic_2,{{"Medical Registration Number", type text}, {"Discipline Type", type text}, {"Start Date", type date}, {"End Date", type date}, {"FTE equivalent", type number}})
in
Output

Output Snapshot:

Regards,
Balakrishnan_J

Did I answer your question? If Yes,
Then mark my post as a solution and click on the Thumbs Up 👍 to give Kudos.
Remember: You can mark multiple answers as a solution...

If your data is sorted as you show, so that rows that should be together are listed together, then you can create the Grouping logic using the fourth and fifth arguments of the Table.Group function:

let

//Replace next line with your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

//set the column data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Index", Int64.Type}, {"Medical Registration Number", type text}, 
        {"Discipline Type", type text}, {"Start Date", type date}, 
        {"End Date", type date}, {"FTE equivalent", type number}}),

//Add a shifted end date column for comparisons
    #"Add Shifted End Date" = Table.FromColumns(
        Table.ToColumns(#"Changed Type")
        & {{null} & List.RemoveLastN(#"Changed Type"[End Date],1)},
        type table[Index=Int64.Type, Medical Registration Number=text, Discipline Type=text,
                        Start Date=date, End Date=date, FTE equivalent=number, Shifted End Date=date]),

//Group rows by the columns we will use to determine the local groups
    #"Grouped Rows" = Table.Group(#"Add Shifted End Date", 
            {"Medical Registration Number", "Discipline Type", "Start Date", "End Date","Shifted End Date"}, {
                {"FTE Equivalent", each List.Sum([FTE equivalent]), type number},
                {"Start Date.1", each List.Min([Start Date]), type date},
                {"End Date.1", each List.Max([End Date]), type date}
            }, GroupKind.Local,(x,y)=>Number.From(

                //logic to replicate the groupings
                    ((x[Medical Registration Number] <> y[Medical Registration Number]) or (x[Discipline Type] <> y[Discipline Type])
                    or (Duration.Days(y[Start Date] - y[Shifted End Date]) <> 1))
                    and (List.RemoveLastN(Record.FieldValues(x),1) <> List.RemoveLastN(Record.FieldValues(y),1))
                )),

//Remove unneeded columns
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Start Date", "End Date", "Shifted End Date"}),

//Rename the Date columns
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Start Date.1", "Start Date"}, {"End Date.1", "End Date"}}),

//Add index column for numbering
    #"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),

//Re-arrange the column order
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Medical Registration Number", "Discipline Type", "Start Date", "End Date", "FTE Equivalent"})
in
    #"Reordered Columns"

Your data

Results from PQ:

Hi JoMont ,

just checking in did any of the solutions shared by the super user help resolve your issue? Or are you still running into challenges? If needed, we can explore alternative approaches together.

Thanks,
Akhil.

Hi JoMont ,

You’re right to pause before dropping those rows  it’s best to understand how many cases exist where everything else matches but the MMM value differs.

You can do this in Power Query with a couple of steps:

1. Remove MMM from the grouping key and then check if more than one distinct MMM exists inside the same group.
2. Flag such rows so you can review them.

Here’s a small M-snippet you can try by adding it after your Duplicate_Logic step:

// Group without MMM to see variations
Check_MMM =  Table.Group(Duplicate_Logic, {"Medical Registration Number", "Discipline Type", "Facility Type", "Start Date", "End Date", "State", "Discipline Group"},  { {"Distinct_MMM", each List.Distinct([MMM]), type list},{"RowCount", each Table.RowCount(_), Int64.Type}}),
// Keep only cases with more than one distinct MMM
Filtered_MMM_Issues = Table.SelectRows(Check_MMM, each List.Count([Distinct_MMM]) > 1)

This will give you a table showing exactly which combinations have multiple MMM values (and how many). You can expand Distinct_MMM if you want to see the different values side by side.

• How many such records exist.
• Where they occur.
• Which MMM values are clashing.

Once you’ve reviewed, you can decide whether to delete, merge, or handle them in a custom way.

Regards,
Akhil.

Hi JoMont ,

Did you get a chance to run the M snippet to check where the MMM mismatches occur? It should give you a clear list of the records with multiple MMM values so you can review them before deciding how to handle.

Regards,
Akhil.