Add missing rows with missing date and time in Power Query

[Anonymous]

Hello everyone,

I need your help please, I have a table with several IDs and for each ID, I have several dates and timestamp and values, normally, I should have for each ID -rows for each hour, but I miss some hours.

So, for those missing hours I need to add in Power Query the missing rows and the values to be taken from last time we had a timestamp.

For example: if I have a row for 6/1/2024  1:00 AM and the next row is 6/2/2024 3:00 PM, I need to add the missing rows for each hour between this interval and the values to be taken from 6/1/2024.

Many thanks.

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create a datetable.

2. Copy -- Table.

Select [Date] and [Value] – Remove Columns.

Select [ID] – Remove Rows – Remove Duplicates.

3. Add Column – Custom Column – Enter Date table name “Date”.

4. Home – Merge Queries -- Merge Queries.

5. Select [Table] – [Value].

Select [Table.Value] – Fill Down

6. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly