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,
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...
@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
Thank you for the code. So far it throws this error:
I tried amending the changed type code to the below - to ensure there were no 'Null" types
ChangedTypes = Table.TransformColumnTypes(#"Removed Columns1", {{"Start Date", type date},{"End Date", type date},{"FTE equivalent", type number},{"Medical Registration Number",type text},{"Discipline Type",type text}}),
But I still can't figure out where the type Null is coming from? Grateful for your help.
FYI, I am doing this for work, and live in Australia. So it might be a little while until I get back to you while I enjoy my weekend!
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
