Forum Discussion
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 one single line on excel is it possible to break that data up so it shows 1 line per day if you know what I mean… I have given an example below
RawData
Date From | Date To | Info | Subt | Description | From | to | Hours | Employee Name | Employee number |
14.08.2022 | 14.08.2022 | Holiday | Holiday |
|
| 12 | John Smith | 12345 | |
12.06.2022 | 15.06.2022 | Holiday | Holiday |
|
| 48 | Bob Jones | 54321 |
This is how I would like it to be formatted
Date From | Date To | Info | Subt | Description | From | to | Hours | Employee Name | SAP No |
14.08.2022 | 14.08.2022 | Holiday | Holiday |
|
| 12 | John Smith | 12345 | |
12.06.2022 | 12.06.2022 | Holiday | Holiday |
|
| 12 | Bob Jones | 54321 | |
13.06.2022 | 13.06.2022 | Holiday | Holiday |
|
| 12 | Bob Jones | 54321 | |
14.06.2022 | 14.06.2022 | Holiday | Holiday |
|
| 12 | Bob Jones | 54321 | |
15.06.2022 | 15.06.2022 | Holiday | Holiday |
|
| 12 | Bob Jones | 54321 |
5 Replies
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
