Forum Discussion
Solved
Breaking up two brackets of dates to individual rows
Hi all I pull a lot of data off a database and do a lot of work around holidays and overtime. The problem I have a lot is when you book say a week off work in one block and export the data its on...
Lorenzo This is amazing thank you so much!!!!!!! Just looking at it now... it seems to pick up single days off well however when there is a block booking it seems to add a day at the end and not pick up the first day, ive had a look in power quays and cant see what needs to be changed see below as an example
Raw Data
| 01.06.2022 | 01.06.2022 | Abs | 100 | Holiday | 12 | John Smith | 12345 | ||
| 26.07.2022 | 29.07.2022 | Abs | 100 | Holiday | 48 | John Smith | 12345 | ||
| 05.08.2022 | 05.08.2022 | Abs | 100 | Holiday | 12 | John Smith | 12345 |
Result
| 01/06/2022 | 01/06/2022 | Abs | 100 | Holiday | 12 | John Smith | 12345 | ||
| 05/08/2022 | 05/08/2022 | Abs | 100 | Holiday | 12 | John Smith | 12345 | ||
| 27/07/2022 | 27/07/2022 | Abs | 100 | Holiday | 12 | John Smith | 12345 | ||
| 28/07/2022 | 28/07/2022 | Abs | 100 | Holiday | 12 | John Smith | 12345 | ||
| 29/07/2022 | 29/07/2022 | Abs | 100 | Holiday | 12 | John Smith | 12345 | ||
| 30/07/2022 | 30/07/2022 | Abs | 100 | Holiday | 12 | John Smith | 12345 |
- Amazing works a dream thank you!!!!
Glad this helped and thanks for Marking solution => helps those who Search...
Attached is an alternative that does it as a single query with https://answers.microsoft.com/en-us/msoffice/forum/all/power-query-get-transform-conditional-replacement/3ca2ca3f-89a2-4463-a0b6-b3e2fac652a4 (a bit complex if you're new to PQ)