Forum Discussion

Terrassa's avatar
Terrassa
Helper I
3 years ago
Solved

Convert a row into a column

Hello,

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

DateStart timeEnd time
John Smith  
01/01/2023  8:0017:03
01/02/2023  8:0217:10
01/03/2023  7:5916:45
01/04/2023  8:1017:00
01/05/2023  7:5517:05
Jane Doe  
01/01/2023  8:0316:55
01/02/2023  8:0117:03
01/03/2023  7:5517:10
01/04/2023  7:5516:57
01/05/2023  8:0018: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

Need Help
  • slorin's avatar
    slorin
    3 years ago

    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 

2 Replies

  • slorin's avatar
    slorin
    Super User
    3 years ago

    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 

  • slorin's avatar
    slorin
    Super User
    3 years ago

    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