Convert a row into a column

[Terrassa]

Hello,

Our payroll application can export the working time per day of all employees in this format:

Date	Start time	End time
John Smith
01/01/2023	8:00	17:03
01/02/2023	8:02	17:10
01/03/2023	7:59	16:45
01/04/2023	8:10	17:00
01/05/2023	7:55	17:05
Jane Doe
01/01/2023	8:03	16:55
01/02/2023	8:01	17:03
01/03/2023	7:55	17:10
01/04/2023	7:55	16:57
01/05/2023	8:00	18:30

I need to do a report calculating the total worked time per employee per month, but to do this I need to add a new column with the employee name in all the rows. How can I get the employee name that is now in the same column than the dates and put it into a new column?

Thank you very much in advance

[slorin]

Another solution

= Table.Group(
   Source,
   {"Date"},
   {{"Data", each Table.Skip(_)}},
   GroupKind.Local,
   (x,y) => Number.From(y[Date] is text)
)

group, if row[date] is type text then new group, else same group

then expand

Stéphane

[slorin · Accepted solution]

Hi

let
Source = Your_Source,
Name = Table.AddColumn(Source, "Name", each if [Start time] = null then [Date] else null),
FillDown = Table.FillDown(Name,{"Name"}),
Filter = Table.SelectRows(FillDown, each [Start time] <> null)
in
Filter

Stéphane