Forum Discussion
Vibee_93
2 years agoFrequent Visitor
Remove Overlapping dates for the same ID
Hi all, I am trying to remove the overalpping dates from my date for the each individuals. I tried searching the other similar queries but mostly, the checks are carried out to the prvious rows o...
- 2 years ago
and pls try this code
let f= (x as table)=> [ a = Table.AddColumn(x,"reg", each [ e = List.Max( List.RemoveLastN(x[End Date],(x)=>x<>[End Date])), f = if [End Date] < e then null else e][f]), b = Table.SelectRows(a, each [reg]<> null)][b], Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdBLDoAgDEXRvTDWpC3/oW6DsP9tCH7wYROHNyekLaWYzSxG4kpuFRLXglv4K+pyewKHeLz182SK4Qk9KWcCZ9bzGVzC5Ht/4nD/PO23f++DGP5zX3fC+SRqPll0q336H689oOv7KKJH7T//e3pGf6PWAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Start Date" = _t, #"End Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Start Date", type date}, {"End Date", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Table",(x)=>f(x) }}), #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Table"}), #"Expanded Table" = Table.ExpandTableColumn(#"Removed Other Columns", "Table", {"Name", "Start Date", "End Date"}) in #"Expanded Table" - 2 years ago
In which case:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdC7DcAgDEXRXagpbD4JLpM1EPuvEYmYpwfVFacwht7DE2IotYj9TRZGhKqgrBdKmgRlTd7MmrO3TX3nKZvPFSXFZmKsx2ZL1Zs29du0bFpR1vW2e9OGshpKevzDUkXH+AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Start Date" = _t, #"End Date" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", Int64.Type}, {"End Date", Int64.Type}}), ChangedType1 = Table.TransformColumnTypes(ChangedType,{{"Start Date", type date}, {"End Date", type date}}), AddedCustom = Table.AddColumn(ChangedType1, "Custom", each {Number.From([Start Date])..Number.From([End Date])}), RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"Name", "Custom"}), ExpandedCustom = Table.ExpandListColumn(RemovedOtherColumns, "Custom"), RemovedDuplicates = Table.Distinct(ExpandedCustom), SortedRows = Table.Sort(RemovedDuplicates,{{"Name", Order.Ascending}, {"Custom", Order.Ascending}}), AddedIndex = Table.AddIndexColumn(SortedRows, "Index", 0, 1, Int64.Type), InsertedSubtraction = Table.AddColumn(AddedIndex, "Subtraction", each [Custom] - [Index], type number), GroupedRows = Table.Group(InsertedSubtraction, {"Name", "Subtraction"}, {{"Start", each List.Min([Custom]), type number}, {"End", each List.Max([Custom]), type number}}), ChangedType2 = Table.TransformColumnTypes(GroupedRows,{{"Start", type date}, {"End", type date}}), RemovedColumns = Table.RemoveColumns(ChangedType2,{"Subtraction"}), SortedRows1 = Table.Sort(RemovedColumns,{{"Name", Order.Ascending}, {"Start", Order.Ascending}}) in SortedRows1
p45cal
2 years agoSolution Supplier
Would it be unhelpful if your data was condensed to:
?
Vibee_93
2 years agoFrequent Visitor
Yes, that would work.
- p45cal2 years agoSolution Supplier
In which case:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdC7DcAgDEXRXagpbD4JLpM1EPuvEYmYpwfVFacwht7DE2IotYj9TRZGhKqgrBdKmgRlTd7MmrO3TX3nKZvPFSXFZmKsx2ZL1Zs29du0bFpR1vW2e9OGshpKevzDUkXH+AA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Start Date" = _t, #"End Date" = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Start Date", Int64.Type}, {"End Date", Int64.Type}}), ChangedType1 = Table.TransformColumnTypes(ChangedType,{{"Start Date", type date}, {"End Date", type date}}), AddedCustom = Table.AddColumn(ChangedType1, "Custom", each {Number.From([Start Date])..Number.From([End Date])}), RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"Name", "Custom"}), ExpandedCustom = Table.ExpandListColumn(RemovedOtherColumns, "Custom"), RemovedDuplicates = Table.Distinct(ExpandedCustom), SortedRows = Table.Sort(RemovedDuplicates,{{"Name", Order.Ascending}, {"Custom", Order.Ascending}}), AddedIndex = Table.AddIndexColumn(SortedRows, "Index", 0, 1, Int64.Type), InsertedSubtraction = Table.AddColumn(AddedIndex, "Subtraction", each [Custom] - [Index], type number), GroupedRows = Table.Group(InsertedSubtraction, {"Name", "Subtraction"}, {{"Start", each List.Min([Custom]), type number}, {"End", each List.Max([Custom]), type number}}), ChangedType2 = Table.TransformColumnTypes(GroupedRows,{{"Start", type date}, {"End", type date}}), RemovedColumns = Table.RemoveColumns(ChangedType2,{"Subtraction"}), SortedRows1 = Table.Sort(RemovedColumns,{{"Name", Order.Ascending}, {"Start", Order.Ascending}}) in SortedRows1