Changing consecutive dates to from dates

[MalAttari]

Hello 
I have following table 

Person	Date	Assignment
Jack	01-Jan	Support
Jack	02-Jan	Support
Jack	03-Jan	Support
Jack	04-Jan	Analysis
Jack	05-Jan	Analysis
Jack	08-Jan	Analysis
Jack	12-Jan	Support

Can I tranform it to 

Person	From	To	Assignment
Jack	01-Jan	03-Jan	Support
Jack	04-Jan	05-Jan	Analysis
Jack	08-Jan	08-Jan	Analysis
Jack	12-Jan	12-Jan	Support

I can do this in DAX struggling to do it in Power Query. Have tried various ways like getting previous / next dates and merging but not getting the result

[DataNinja777 · Accepted solution]

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,

[slorin · Accepted solution]

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

[slorin · Accepted solution]

@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

[dufoq3 · Accepted solution]

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

[MalAttari]

Thanks @dufoq3, Works too. Going to see if there is a performace advantage of any. Have a few million rows. 

[dufoq3]

You're welcome.

[slorin · Accepted solution]

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

[MalAttari]

Thanks Stéphane, Going to go through it today.

[DataNinja777 · Accepted solution]

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,

[MalAttari]

Thanks DataNinja777 going to go through it today

[speedramps]

@slorin @DataNinja777 

When I try to copy and paste the M code it is not transposing.

Please can you save and share your PBIX solution with view access on OneDrive or Dropbox

You can download and use this PBIX, edit it and then save it with view access on you Onedrive or drop box. Then post the link in the chat.
Click here 

Thank you. I look forward to it.

[slorin · Accepted solution]

@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