Forum Discussion
Merging records that have continuous dates
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
Thanks Daryl. Looking up the group by and min/max date from/to features now (going to try to learn this step by step as I don't really understand the code). One thing that has come to mind when i'm thinking about this though is how it would deal with a scenario where the dataset included multiple date ranges for the same person and reason code that weren't consecutive? Just thinking the min/max might take the min from the earliest one and the max from the latest one even if there was a gap between the two ranges.
Hi Jocky - so in your example if Bill second record with reason type 1 was on the 10/04/2022, you want to show two rows? Hmm - tricky one? Again Group By might be able to help but you will need to develop function to process the table created in the group by function.
Is there a scenario we the date overlap e.g. the instead of 09/04/2022 or 10/04/2022 could the value be 07/04/2022 or earlier?
Thanks Daryl. Yeah, that's right. In the scenario described there would be two rows for Bill.