Forum Discussion
Add rows with missing dates in Power Query
pls try this
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 |
Super Userpls 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?- Ahmedx
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
Yes, correct Ahmedx. I forgot to update the previous step name for the grouping step.
Can you please suggest some resources for learning advanced power query.
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?