Forum Discussion

Jocky's avatar
Jocky
Frequent Visitor
4 years ago
Solved

Merging records that have continuous dates

Hi there,   I have a dataset that looks something like this (note dates are in UK format):     Employee Reason code Date from Date to #1 Bill 1 05/04/2022 08/04/2022 #2 Bill 2 ...
Need Help
  • artpil's avatar
    artpil
    4 years ago

    Hi,

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date from", type date}, {"Date to", type date}}, "en-GB"),
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Employee", Order.Ascending}, {"Date from", Order.Ascending}}),
    Index = Table.Buffer(Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type)),
    #"Added Custom" = Table.AddColumn(Index, "ModIndex", each try if Index[Employee]{[Index]-1}=[Employee] and Index[#"Reason code"]{[Index]-1}=[#"Reason code"] and Index[#"Date to"]{[Index]-1}=Date.AddDays([#"Date from"],-1) then [Index]-1 else [Index] otherwise [Index]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Employee", "Reason code", "ModIndex"}, {{"Date from", each List.Min([Date from]), type nullable date}, {"Date to", each List.Max([Date to]), type nullable date}})
in
    #"Grouped Rows"

    You can sort the table by name and start date. Then add index column, add custom column which cheks if previous row has the same name, reason and date to is one day smaller then  date from. If yes get previous row idex else current row index. Then gropu by modyfied index column and get min and max from date field respectively.

    Hope this will help.

    Artur

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Jocky - I think you could achieve the desire result using the Group By functionality.  You will group by "Employee" and "Reason", and use the Min Date From and Max Date to.  Try the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsrMyVHSUTIEYgNTfQMTfSMDIyOQAJBjCuHE6sCVgWQMLPUNzGDKkDggZV6JuanFMHWG+gZGcHVG+gbGCHXBiXkplUjWGsLVmcM5IHWOeSlFqeUwA1EUmqEo9EJynzGSN0DuM0Eoc83NTYRZawx3EZBjgnBeLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Reason code" = _t, #"Date from" = _t, #"Date to" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reason code", Int64.Type}}),
    #"Parsed Date" = Table.TransformColumns(#"Changed Type",{{"Date from", each Date.From(DateTimeZone.From(_)), type date}, {"Date to", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Grouped Rows" = Table.Group(#"Parsed Date", {"Employee", "Reason code"}, {{"Date from", each List.Min([Date from]), type date}, {"Date to", each List.Max([Date to]), type date}})
in
    #"Grouped Rows"