Breaking up two brackets of dates to individual rows

[SBaldwin93]

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

[#2]

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

[#3]

@L z.  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

[#4 · Accepted solution]

@SBaldwin93 

You're absolutely right - my bad. Fixed in attached version

[#5]

Amazing works a dream thank you!!!!

[#6]

@SBaldwin93 

Glad this helped and thanks for Marking solution => helps those who Search...

Attached is an alternative that does it as a single query with conditional replacement (a bit complex if you're new to PQ)