Add a new row per ID with (last date +1 day)

[TerriAki]

Hi, 

Any help would be appreciated on the following problem please:

Trying to add a new row per ID with the date value as the last date plus 1 day.  Expected result is in red font, the actual data has over 4 million rows:

T&A Table:

Employee ID	Date	Status	Week Ending
1	28/03/2020	RD	28/03/2020 00:00
1	29/03/2020	S	04/04/2020 00:00
1	30/03/2020	S	04/04/2020 00:00
1	31/03/2020	S	04/04/2020 00:00
1	01/04/2020	RD	04/04/2020 00:00
1	02/04/2020
2	28/03/2020	S	04/04/2020 00:00
2	29/03/2020	RD	04/04/2020 00:00
2	30/03/2020	TW	11/04/2020 00:00
2	31/03/2020	TW	11/04/2020 00:00
2	01/04/2020	1	11/04/2020 00:00
2	02/04/2020	RD	11/04/2020 00:00
2	03/04/2020	1	11/04/2020 00:00
2	04/04/2020
3	28/03/2020	RD	11/04/2020 00:00
3	29/03/2020	AL	18/04/2020 00:00
3	30/03/2020	HA	18/04/2020 00:00
3	31/03/2020	TW	18/04/2020 00:00
3	01/04/2020	RD	18/04/2020 00:00
3	02/04/2020	TW	18/04/2020 00:00
3	03/04/2020	RD	18/04/2020 00:00
3	04/04/2020	1	25/04/2020 00:00
3	05/04/2020	TW	25/04/2020 00:00
3	06/04/2020	1	25/04/2020 00:00
3	07/04/2020	RD	25/04/2020 00:00
3	08/04/2020	TW	25/04/2020 00:00
3	09/04/2020

[TerriAki]

This is perfect, thank you for the quick response. 

[BA_Pete · Accepted solution]

Hi @TerriAki ,

Paste this into a new blank query using Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJNC4JAEAbgvyJ7FpzZ1bJugkJBpww8iAehJaSyIDv075sOkrs6uuDn7sO7O6NlKbL78/b4aO3tU+GLtO403fKu7t4veii0vnpZe27ai6j8UiCNyTgAFUiQQC/H1BjxALYAf7oZ0JxOCAM6JqQCZ4muErCf6Dc6SaVdExsq7ZLmQ42aTgVdEDmKztSoCmeltOvnqXIJVZNfn6fDViWHH40ZarRql8zScas4Ov4BeDrs1UKqck8NrbbKiJORvT5PV86ha3unPI2X16++", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    promoteHeads = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    chgTypes = Table.TransformColumnTypes(promoteHeads,{{"Week Ending", type datetime}, {"Date", type date}}),
    groupRows = Table.Group(chgTypes, {"Employee ID"}, {{"data", each _, type table [Employee ID=nullable text, Date=nullable date, Status=nullable text, Week Ending=nullable datetime]}}),
    addNestedRow =
        Table.TransformColumns(
            groupRows,
            {"data", each
            Table.InsertRows(_, 0, 
                {
                    [Employee ID = List.Max(_[Employee ID]), Date = Date.AddDays(List.Max(_[Date]), 1), Status = null, Week Ending = null]
                }
            )}
        ),
    expandDataColumn = Table.ExpandTableColumn(addNestedRow, "data", {"Date", "Status", "Week Ending"}, {"Date", "Status", "Week Ending"})
in
    expandDataColumn

This gives me the following output:

Pete