Transforming single row into multiple row

[Anonymous]

I have a large table of data as below, 

refrence no.	booking name	Booking type	start date	duration	end date	venue	pax
ab12345	booking 1	a	23/03/2020	2	25/03/2020	venue 1	100
ab456456	booking 2	b	23/03/2020	3	26/03/2020	venue 1	50

I need to transofrm it to show [pax] per [refrenceno.] per day. something like below

Date	refrence no.	booking name	Booking type	venue	pax
23/03/2020	ab12345	booking 1	a	venue 1	100
23/03/2020	ab456456	booking 2	b	venue 1	50
24/03/2020	ab12345	booking 1	a	venue 1	100
24/03/2020	ab456456	booking 2	b	venue 1	50
25/03/2020	ab12345	booking 1	a	venue 1	100
25/03/2020	ab456456	booking 2	b	venue 1	50
26/03/2020	ab456456	booking 2	b	venue 1	50

i cant work out to insert the extra rows for the dtae and apply the values in to the new rows. 

any help greatly appreciated

steve

[ibarrau · Accepted solution]

Hi! yes you can do that. Go to edit queries. Then add a custom column with a code like this:

List.Transform({Number.From([start date])..Number.From([end date])}, each Date.From(_))

This will create a list for each row containing the values between those dates. Then you can expand the list clicking on the arrows button. The result will be the expected one.

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thats amazing thanks so much worked perfectly