Split Table into individual lines

[Kcroft9]

Hi,

I have a table which shows annual leave for employees. each line is for an event, so there are 3 columns for the event, start date and end date and duration. what I would like to do is get that split out so there is a line entry for each day booked.

For example. if and annual leave event was for 3 days, 01/05/2022-03/05/2022, in my current table it shows that in 1 line with the duration column reading 3.Plus all the other info such as names etc.
what I would like is for there to be 3 lines showing this event. Line 1 would read 01/05/2022, duration 1, plus all the other info such as names etc. Line 2 would read 02/05/2022, duration 1, plus all the other info such as names etc. Line 3 would read 03/05/2022, duration 1, plus all the other info such as names etc.

and so on.

I hope that makes sense and i have attach a data file sample to support anyone who may be able to help me here.

Many thanks in advance.

[Bo_Ry]

Please try at

A12:Axx
=IF(ROWS(A$11:A11)>SUM(ROUNDUP(Table2[DurationDays],)),"",LOOKUP(ROWS(A$11:A11)-1,MMULT(--(ROW(Table2)>TRANSPOSE(ROW(Table2))),ROUNDUP(Table2[DurationDays],)),Table2[Id]))

B12:Mxx
=IF($A12="","",VLOOKUP($A12,Table2,MATCH(B$11,Table2[#Headers],),))

G12:Gxx
=IF($A12="","",WORKDAY(VLOOKUP($A12,Table2,7,),COUNTIF(A$11:$A11,A12)))

[Kcroft9]

Hi Bo_Ry,

Thank you for your reply. What you have suggested does work in formula version, however I must apologies as I missed a couple of key details .

The table at the top of the page, the one I want to transpose is actually in a power Query connection and not on a worksheet.

Do you know what the code equivalent is to create the transposed table in Power Query?

Many thanks and apologies for missing that part out,

[Bo_Ry]

Try

Please Login or Register  to view this content.

[Kcroft9]

Hi,

I have modified my attached file that has those tables as connection files. I don't think I gave enough of an explanation at the beginning of this thread.

In my live file my data is a PowerQuery table connection and nota table in the workbook like in this file. Obviously I cannot send the file with the external links because it would not load for you. I have only added the tables in this sample file to allow me to turn it into a PowerQuery connection as you can see. It is that table connection that I require to “transposed" but directly in PowerQuery.
I do not want the output table in the workbook tab, just as a Table connection in PowerQuery.

I hope that makes sense, it felt hard to explain. I have attach the new data file sample to support,

Many thanks in advance.

[Kcroft9]

HI,

Could someone advise if this is posted to the wrong forum. I still require support if anyone is able to help,

Thank you

[AliGW]

It's OK where it is and you have now bumped it, so let's wait and see.

[Kcroft9]

Bumping this as I still need help