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,
Thank you, but I need some additional help. I have this existing code, which creates the column "FTE Equivalent" and I'm not sure how to work in your 'let t = ' statements in the Source line, once I've created the "FTE Equivalent" column.
Here is my existing 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"})
in
#Removed Columns1
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