Forum Discussion
Group rows with same dates and consecutive dates
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...
Hi JoMont,
Hope you are doing well.
Open Advanced Editor of Power Query and copy paste the below M-code,
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Placement Number", "Column1", "Facility Name", "Address Line 1", "Address Line 2", "Street Address / Address Line 3", "Suburb/town", "Postcode#(tab)"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Discipline Type", Text.Trim, type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Trimmed Text", "Discipline Group", each if [Discipline Type] = "Acute Medicine" then "Medical" else if [Discipline Type] = "Cardiology" then "Medical" else if [Discipline Type] = "General Medicine" then "Medical" else if [Discipline Type] = "Gastroenterology" then "Medical" else if [Discipline Type] = "Geriatrics" then "Medical" else if [Discipline Type] = "Internal Medicine" then "Medical" else if [Discipline Type] = "Medical or Ward Call" then "Medical" else if [Discipline Type] = "Paediatrics" then "Medical" else if [Discipline Type] = "Palliative Care" then "Medical" else if [Discipline Type] = "Respiratory" then "Medical" else if [Discipline Type] = "Rehabilitation" then "Medical" else if [Discipline Type] = "Rural Medicine" then "Medical" else if [Discipline Type] = "Sub Acute" then "Medical" else if [Discipline Type] = "Anaesthetics" then "Anaesthetics" else if [Discipline Type] = "ENT" then "Surgical" else if [Discipline Type] = "General Surgery" then "Surgical" else if [Discipline Type] = "General surgery" then "Surgical" else if [Discipline Type] = "Neurosurgery" then "Surgical" else if [Discipline Type] = "Orthopaedics" then "Surgical" else if [Discipline Type] = "Plastic Surgery" then "Surgical" else if [Discipline Type] = "Emergency Medicine" then "Emergency" else if [Discipline Type] = "Intensive Care Unit" then "ICU" else if [Discipline Type] = "Psychiatry" then "Psychiatry" else if [Discipline Type] = "Mental Health" then "Psychiatry" else if [Discipline Type] = "General Practice" then "Community" else if [Discipline Type] = "Rural Community" then "Community" else if [Discipline Type] = "Residency Program" then "Community" else if [Discipline Type] = "PIERCE" then "Community" else if [Discipline Type] = "Primary Health" then "Community" else if [Discipline Type] = "John Flynn Prevocational Doctor Program" then "Community" else if [Discipline Type] = "Rural Junior Doctor Training Innovation Fund" then "Community" else if [Discipline Type] = "Rural Community Residency Program" then "Community" else if [Discipline Type] = "Obstetrics and Gynaecology" then "Obstetrics & Gynaecology" else if [Discipline Type] = "Radiology" then "Radiology" else if [Discipline Type] = "Clinical Service Improvement" then "Miscellaneous" else if [Discipline Type] = "Relieving" then "Miscellaneous" else if [Discipline Type] = "Research" then "Miscellaneous" else if [Discipline Type] = "Composite" then "Miscellaneous" else if [Discipline Type] = "Medical Education" then "Miscellaneous" else if [Discipline Type] = "Oncology" then "Medical" else if [Discipline Type] = "Neurology" then "Medical" else null),
#"Added Custom" = Table.AddColumn(#"Added Conditional Column", "FTE equivalent", each if [Full time Weeks] > 1 then [Full time Weeks] else ((Duration.Days ([End Date] - [Start Date])+1)/7)*[Full time Weeks]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Full time Weeks"}),
// Sorting by Medical Registration Number and Start Date is important here.
// Without applying sorting logic, sometimes gives you different result.
Sort_Logic = Table.Sort(#"Removed Columns1",{{"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
Note: Kindly share excel file if the above code does not work.
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...
Thanks so much for the amended code, it works perfectly. However, I lose some of the columns I need, such as Discipline Group, State, MMM. I tried to put this in my original post but it told me weirdly I was over the character limit.
My Data set actually looks like this
| Medical Registration Number | Discipline Type | Facility Type | Start Date | End Date | State | MMM | Discipline Group | FTE equivalent |
| MED001 | General Medicine | Hospital | 31/10/2022 | 15/01/2023 | WA | MMM3 | Medical | 11 |
| MED002 | Emergency Medicine | Hospital | 31/10/2022 | 15/01/2023 | WA | MMM3 | Emergency | 11 |
| MED002 | General Practice | General Practice | 16/01/2023 | 30/12/2023 | WA | MMM3 | Community | 49.85714 |
| MED003 | General Practice | General Practice | 2/01/2023 | 30/12/2023 | WA | MMM5 | Community | 25.92857 |
| MED003 | Emergency Medicine | Hospital | 2/01/2023 | 30/12/2023 | WA | MMM4 | Emergency | 25.92857 |
| MED004 | Emergency Medicine | Hospital | 31/10/2022 | 15/01/2023 | WA | MMM3 | Emergency | 11 |
| MED005 | Obstetrics and Gynaecology | Hospital | 23/02/2022 | 15/01/2023 | WA | MMM3 | Obstetrics & Gynaecology | 46.71429 |
| MED005 | Obstetrics and Gynaecology | Hospital | 16/01/2023 | 15/01/2024 | WA | MMM3 | Obstetrics & Gynaecology | 52.14286 |
| MED006 | Emergency Medicine | Hospital | 2/11/2023 | 4/02/2024 | NT | MMM6 | Emergency | 12 |
| MED007 | General Practice | General Practice | 17/10/2022 | 7/01/2023 | WA | MMM3 | Community | 11.85714 |
| MED008 | General Medicine | Hospital | 31/10/2022 | 15/01/2023 | WA | MMM2 | Medical | 11 |
| MED009 | Emergency Medicine | Hospital | 16/01/2023 | 26/03/2023 | WA | MMM3 | Emergency | 10 |
| MED009 | Clinical Service Improvement | Hospital | 27/03/2023 | 11/06/2023 | WA | MMM3 | Miscellaneous | 11 |
| MED009 | Obstetrics and Gynaecology | Hospital | 12/06/2023 | 20/08/2023 | WA | MMM3 | Obstetrics & Gynaecology | 10 |
| MED009 | General Medicine | Hospital | 21/08/2023 | 29/10/2023 | WA | MMM3 | Medical | 10 |
| MED009 | General Medicine | Hospital | 30/10/2023 | 14/01/2024 | WA | MMM3 | Medical | 11 |
| MED010 | General Surgery | Hospital | 6/02/2023 | 11/06/2023 | WA | MMM2 | Surgical | 18 |
| MED010 | Emergency Medicine | Hospital | 12/06/2023 | 21/08/2023 | WA | MMM3 | Emergency | 10.14286 |
| MED010 | Emergency Medicine | Hospital | 21/08/2023 | 8/01/2024 | WA | MMM2 | Emergency | 10.07143 |
| MED010 | Emergency Medicine | Hospital | 21/08/2023 | 8/01/2024 | WA | MMM3 | Emergency | 10.07143 |
1. How do I get the extra columns carried across in the grouping?
2. I note that MED010 has two placements of Emergency Medicine at the same time across two different MMM (must be a border region). This will cause issues for grouping. If it's a one off in the data set I can just change it manually, but how do I check the entire dataset for this kind of occurrence before I group?
Thanks for your help