Remove Overlapping dates for the same ID

[Vibee_93]

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 only. But I need to search all the previous rows of the same user if there are any overlap and need to remove the overlapping rows. Please refere the below sample data and expected output.

Thanks in advance.

[Ahmedx · Accepted solution]

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"

[p45cal]

Would it be unhelpful if your data was condensed to:

?

[Vibee_93]

Yes, that would work.

[p45cal · Accepted solution]

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

[Ahmedx · Accepted solution]

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"

[Ahmedx]

pls try this

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}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Table", (x)=>[
 a = Table.FromRecords({ Table.Min(x,"Start Date")})[End Date
]{0},
 b = Table.SelectRows(x, (z)=> z[End Date] >= a )
][b]
}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Table"}),
    #"Expanded Table" = Table.ExpandTableColumn(#"Removed Other Columns", "Table", {"Name", "Start Date", "End Date"})
in
    #"Expanded Table"

[Chewdata]

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"