Forum Discussion

Vibee_93's avatar
Vibee_93
Frequent Visitor
2 years ago
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...
  • Ahmedx's avatar
    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"
  • p45cal's avatar
    p45cal
    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