Forum Discussion
Solved
Group rows with same dates and consecutive dates
Hi, I have data with medical registration numbers, discipline types, start and end dates and FTE equivalent, about medical training placements. There are other columns, but they are not material to ...
Hi JoMont,
Hope you are doing well.Open advanced editor in power query and copy paste the below M-code.
Duplicate Entry is removed and all columns are retained.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZRNbsIwEIWvErEmyJ78LyuKaBdpK1Gpi5ZFGiwUKXFQEpC4Tc/Sk3VMgdgmfyA2yI7w+2bmPfvzcxTOHgmho/FozjgrotQI2SqJE87w01NebpIqSnFpUZMSEwgAbqhjEio2Fm4+HvAnDEOxPpw9/J/S0XJ8VBdHZhkr1ozH+9v1f39knQbGqYO3IoqrJGbNn6griVvEpNDUyTTPsi1PKsGxg4nveNSuadZgGoE+mqPRwJkEgDyV1je/fo6tTe+SY9/Hpw6XRK+v32XFqiKJSyPiK2O+5xGL8zRf71USWCaBAYmQ9L62GARXk7TdCXoHwW1VKGk5V2FfXYUDEyzCd+sq3GGu0jOd2MeBCPrL+z/d1acNNcEbfic8yVXitbgqp5RS/U74d3hDoPUNCQZMS/EKcGP1ZpOohGmacAE3FqzY4WiM52xT5DuWMV5p6fQkefSIuI3vYVLGLE0jzvJt2dDR4BSCBABiEr+J1pdBvdtut4BKGAiO1nW8+FepiyfqLEjtpnvVlgUE1eqLLZpZaOPCWRHoNEcETRw9qfuqem/SFD9oix9q0rT7P4ijSOPqckpwySF4La17cxr6OXKWfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Medical Registration Number" = _t, #"Discipline Type" = _t, #"Facility Type" = _t, #"Start Date" = _t, #"End Date" = _t, State = _t, MMM = _t, #"Discipline Group" = _t, #"FTE equivalent" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Medical Registration Number", type text}, {"Discipline Type", type text}, {"Facility Type", type text}, {"Start Date", type date}, {"End Date", type date}, {"State", type text}, {"MMM", type text}, {"Discipline Group", type text}, {"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}}), Duplicate_Logic = Table.Distinct(Sort_Logic, {"Medical Registration Number", "Discipline Type", "Facility Type", "Start Date", "End Date", "State", "Discipline Group", "FTE equivalent"}), Group_Logic_1 = Table.Group(Duplicate_Logic, {"Medical Registration Number", "Discipline Type","Facility Type", "State", "MMM", "Discipline Group"}, {{"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", "Facility Type", "Start Date", "End Date", "State", "MMM", "Discipline Group"}, {{"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 OutputRegards,
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: