How to add a timesnap in Power Query manually?

[hish]

Hello everyone,

I have a data set with several timesnaps and the according informations. Now I want to add a time snap at 10pm for every day. The information of the other column for the 10pm time snap should be taken from the rows before.

For example:

In this case, I want to have an additional raw between raw 1 and raw 2 with the time snap at 01.01.2025 22:00:00 | 9

Anyone an idea how to add a aditional raw?

[v-kathullac]

Thanks @johnbasha33  for Addressing the issue.

Hi @hish ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Thanks @johnbasha33  for Addressing the issue.

Hi @hish ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Thanks @johnbasha33  for Addressing the issue.

Hi @hish ,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.

If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Regards,

Chaithanya.

[johnbasha33 · Accepted solution]

@hish 

1. Duplicate the Table

In Power Query, right-click your original table → Duplicate.
(You’ll work with two versions.)

2. Create the "10PM" Rows

In the new copy, do the following:

• Group the data by Date (ignore time part).
  (e.g., group by Date.From([Timestamp]))

• For each group:

  • Find the latest time <= 22:00 (or simply the latest time for the day).

Create a new row with Timestamp = Date + 22:00:00
and Value = last known value before 22:00.

let
Source = YOUR_TABLE,
AddDate = Table.AddColumn(Source, "DateOnly", each Date.From([Timestamp])), // just the date
Grouped = Table.Group(AddDate, {"DateOnly"}, {
{"AllRows", each
let
RowsBefore22h = Table.SelectRows(_, each Time.From([Timestamp]) <= #time(22,0,0)),
LastRow = Table.Last(RowsBefore22h),
NewRow = [
Timestamp = [DateOnly] & #time(22,0,0),
Value = LastRow[Value]
]
in
{NewRow}
, type list}
}),
Expanded = Table.ExpandListColumn(Grouped, "AllRows")
in
Expanded

Combine Original + 10PM Rows

• Go back to your original table.

• Append (combine) with this "10 PM Rows" table.

Sort everything by Timestamp again.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!