Forum Discussion
Transforming single row into multiple row
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
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.
2 Replies
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.