Forum Discussion
Add rows with missing dates in Power Query
pls try this
post an example and show what result you are expecting
Hi Ahmedx ,
Input Data
| Employee ID | Date | Time In | Time Out |
| 100 | 27-12-2023 | 10 AM | 5 PM |
| 100 | 31-12-2023 | 9 AM | 7 PM |
| 200 | 27-12-202 | 9 AM | 3 PM |
| 200 | 31-12-2023 | 9 AM | 9 PM |
Output
| Employee ID | Date | Time In | Time Out |
| 100 | 01-12-2023 | ||
| 200 | 01-12-2023 | ||
| 100 | 02-12-2023 | ||
| 200 | 02-12-2023 | ||
| 100 | 03-12-2023 | ||
| 200 | 03-12-2023 | ||
| 100 | 04-12-2023 | ||
| 200 | 04-12-2023 | ||
| .. | |||
| .. | |||
| 100 | 22-12-2023 | ||
| 200 | 22-12-2023 | ||
| 100 | 23-12-2023 | ||
| 200 | 23-12-2023 | ||
| 100 | 24-12-2023 | ||
| 200 | 24-12-2023 | ||
| 100 | 25-12-2023 | ||
| 200 | 25-12-2023 | ||
| 100 | 26-12-2023 | ||
| 200 | 26-12-2023 | ||
| 100 | 27-12-2023 | 10 AM | 5 PM |
| 200 | 27-12-2023 | 9 AM | 3 PM |
| 100 | 28-12-2023 | ||
| 200 | 28-12-2023 | ||
| 100 | 29-12-2023 | ||
| 200 | 29-12-2023 | ||
| 100 | 30-12-2023 | ||
| 200 | 30-12-2023 | 9 AM | 7 PM |
| 100 | 31-12-2023 | 9 AM | 7 PM |
| 200 | 31-12-2023 | 9 AM | 9 PM |
pls try this
Thank you so much for the effort and support Ahmedx.
One thing: I removed last year's data and some unwanted fields before grouping, but after expanding the data, last year's data and removed fields are also showing.
Can you please guide me to learn advanced power-query skills?If you removed last year's data and some unnecessary fields before grouping, then after expanding the data these fields should not appear.
give an example or your file I will help
Hi Ahmedx,
There is one issue in the solution: missing dates are showing based on the max date; if there is no record for one employee for the last week, then those days are not showing. Can you please suggest a solution for this?