Forum Discussion
Solved
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...
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"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
Hey,
I think the following code will solve your problem.
First, it will create a list of dates between start and end date.
Secondly, It will acumulate a lists from previous rows of the same Name.
Third, it wil filter all rows where the start date is in the list of dates.
let
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}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Start Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Name"}, {{"Table", each _, type table [Name=nullable text, Start Date=nullable date, End Date=nullable date]}}),
add_table_Index = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Table], "Index", 1, 1, type text)),
#"Removed Other Columns" = Table.SelectColumns(add_table_Index,{"Index"}),
#"Expanded Index" = Table.ExpandTableColumn(#"Removed Other Columns", "Index", {"Name", "Start Date", "End Date", "Index"}, {"Name", "Start Date", "End Date", "Index"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Name", type text}, {"Index", Int64.Type}, {"Start Date", type date}, {"End Date", type date}}),
add_UniqueId = Table.AddColumn(#"Changed Type1", "UniqueId", each [Name] & "-" & Text.From([Index]), type text),
add_ListDates = Table.AddColumn(add_UniqueId, "ListDates", each List.Dates(Date.From([Start Date]), Duration.Days([End Date] - [Start Date]) + 1, #duration(1,0,0,0))),
#"Sorted Rows1" = Table.Sort(add_ListDates,{{"Index", Order.Ascending}}),
#"Grouped Rows1" = Table.Group(#"Sorted Rows1", {"Name"}, {{"Table", each _, type table [Name=nullable text, Start Date=nullable date, End Date=nullable date, Index=nullable text, UniqueId=text, ListDates=list]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom1", each
let
currentTable = [Table],
cumulativeTable = Table.AddColumn(currentTable, "ConcatDatesList", each
let
currentIndex = [Index],
previousRows = Table.SelectRows(currentTable, each [Index] < currentIndex),
concatenatedList = List.Combine(previousRows[ListDates])
in
concatenatedList
)
in
cumulativeTable),
#"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom1", "Custom1", {"Name", "Start Date", "End Date", "Index", "UniqueId", "ListDates", "ConcatDatesList"}, {"Name.1", "Start Date", "End Date", "Index", "UniqueId", "ListDates", "ConcatDatesList"}),
add_Filter = Table.AddColumn(#"Expanded Custom1", "Filter", each List.Contains([ConcatDatesList], [Start Date])),
#"Filtered Rows" = Table.SelectRows(add_Filter, each ([Filter] = false)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Table", "Name.1", "Index", "ListDates", "ConcatDatesList", "Filter"})
in
#"Removed Columns"