Forum Discussion
Add missing rows with missing date and time in Power Query
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.
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
1 Reply
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