Changing consecutive dates to from dates

Hi MalAttari ,

 

To achieve the desired transformation in Power Query, start by loading your table into Power Query and sorting it by the Person and Date columns in ascending order. After sorting, you need to identify consecutive rows with the same Assignment while ensuring that dates are sequential.

First, create an Index column to assist in identifying groups of consecutive assignments. To do this, go to the Add Column tab and select Index Column (From 0). Next, add a Custom Column to calculate a GroupID that identifies each consecutive block of the same assignment. You can do this using the following steps.

Go to Add Column > Custom Column and use the formula below:

 

 

let
    // Load your table
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // Sort the table by Person and Date
    SortedTable = Table.Sort(Source, {{"Person", Order.Ascending}, {"Date", Order.Ascending}}),

    // Buffer the table to improve performance and prevent referencing issues
    BufferedTable = Table.Buffer(SortedTable),

    // Add an Index column to help track row positions
    IndexedTable = Table.AddIndexColumn(BufferedTable, "Index", 0, 1, Int64.Type),

    // Add a GroupID column to identify consecutive assignments
    GroupedTable = Table.AddColumn(
        IndexedTable,
        "GroupID",
        each if [Index] = 0 then 0
        else if [Assignment] = BufferedTable{[Index]-1}[Assignment] 
             and [Person] = BufferedTable{[Index]-1}[Person] 
             and Date.AddDays(BufferedTable{[Index]-1}[Date], 1) = [Date] 
        then BufferedTable{[Index]-1}[GroupID]
        else BufferedTable{[Index]-1}[GroupID] + 1
    ),

    // Group by Person and GroupID, and calculate From and To dates
    GroupedResult = Table.Group(
        GroupedTable,
        {"Person", "GroupID"},
        {
            {"From", each List.Min([Date]), type date},
            {"To", each List.Max([Date]), type date},
            {"Assignment", each List.First([Assignment]), type text}
        }
    ),

    // Remove the GroupID column
    FinalTable = Table.RemoveColumns(GroupedResult, {"GroupID"})
in
    FinalTable

 

 

 

Best regards,

Hi MalAttari 

 

let
Source = Your_Source,
Index = Table.AddIndexColumn(Your_Source, "Index", 0, 1, Int64.Type),
Group = Table.Group(
    Index,
    {"Person", "Assignment", "Date", "Index"}, 
    {{"From", each List.Min([Date]), type nullable date}, {"To", each List.Max([Date]), type nullable date}},
    GroupKind.Local, 
    (x,y) => Number.From((y[Index]-x[Index]) <> Number.From(Duration.Days(y[Date]-x[Date])) 
                          or y[Assignment] <> x[Assignment])),
Columns = Table.SelectColumns(Group,{"Person", "From", "To", "Assignment"})
in
Columns

Stéphane

speedramps 

You have to adapt this code

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kpMzlbSUTIw1AciIwMjUyAnuLSgIL+oRClWByFvREDemIC8CZK8Y15iTmVxZjGKAlNCCiwIKDDE6sRYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Date = _t, Assignment = _t]),
TypeDate = Table.TransformColumnTypes(Source, {{"Date", type date}}, "fr-FR"),
Index = Table.AddIndexColumn(TypeDate, "Index", 0, 1, Int64.Type),
Group = Table.Group(Index, {"Person", "Assignment", "Date", "Index"}, {{"From", each List.Min([Date]), type nullable date}, {"To", each List.Max([Date]), type nullable date}}, GroupKind.Local, (x,y) => Number.From((y[Index]-x[Index])<>Number.From(Duration.Days(y[Date]-x[Date])) or y[Assignment]<>x[Assignment])),
Columns = Table.SelectColumns(Group,{"Person", "From", "To", "Assignment"})
in
Columns

 Stéphane

Hi MalAttari, another solution:

 

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kpMzlbSUTIw1PVKzAMygksLCvKLSpRidRByRnjkjPHImUDlHPMScyqLM4tRJE3xSVrgkTTEcE4sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Date = _t, Assignment = _t]),
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    GroupedRows = Table.Group(AddedIndex, {"Person", "Date", "Assignment", "Index"}, {{"T", each let a = Table.First(_), b = [Person = a[Person], From = a[Date], To = Table.Last(_)[Date], Assignment = a[Assignment]] in b, type table}}, 0,
        (x,y)=> Byte.From( y[Person] <> x[Person] or y[Assignment] <> x[Assignment] or Duration.TotalDays(Date.From(y[Date]) - Date.From(x[Date]))  > (y[Index] - x[Index]) ) ),
    CombinedT = Table.FromRecords(GroupedRows[T])
in
    CombinedT