Forum Discussion
Breaking up two brackets of dates to individual rows
Hi SBaldwin93
With Get & Transform aka Power Query (attached):
Put your raw data in the Blue table in sheet Raw Data
Switch to sheet ExpandedDates
Right-click in the Green table > Refresh
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)