Forum Discussion

JoMont's avatar
JoMont
Frequent Visitor
1 year ago
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 ...
Need Help
  • Balakrishnan_J's avatar
    Balakrishnan_J
    1 year ago

    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
    Output

     

     

    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...

Anonymous's avatar
Anonymous
Not applicable
11 months ago

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.

JoMont's avatar
JoMont
Frequent Visitor
10 months ago

Hi Akhil,

I have finally had a chance to implement the code. It works well, except it only solves half the question. This code checks for duplicate MMMs where the start and end dates are the same - and it found one case. The other half to check for is where there are consecutive terms in the same discipline by the same student but different MMMs, as in the provided example.

Thanks