Kcroft9
05-25-2022, 11:38 PM
Hi,
I have a table which shows annual leave for employees. each line is for an event, so there are 3 core columns for each line event, start date, end date, duration and employeeID. The rest of the columns are other information that is required but those 4 columns listed are the unique events that would be used I think to separate a line item. what I would like to do within PowerQuery is get that split out so there is a line entry for each day booked.
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" in some way by those key events but directly in PowerQuery.
For example. if an annual leave or absence 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.
I do not want the output table in a 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.
I have a table which shows annual leave for employees. each line is for an event, so there are 3 core columns for each line event, start date, end date, duration and employeeID. The rest of the columns are other information that is required but those 4 columns listed are the unique events that would be used I think to separate a line item. what I would like to do within PowerQuery is get that split out so there is a line entry for each day booked.
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" in some way by those key events but directly in PowerQuery.
For example. if an annual leave or absence 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.
I do not want the output table in a 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.