Move rows up to row above


At my company we use an Economy-system with a webbased timetracking of work by employees.
And you can add notes to the time tracked. See red and yellow markings in image below.
When others have to look at time used for an order they can export an Excel-file with data.
But it is formatted as below. A single line note is split into 1 or more lines under the name of the employee.
I would like to have these notes on one line after the number.
I did not easily find a way to do this, so I would like some input on how I could achive this.

I have included an input and desired output file below.

Desired output

Here is how I came up with to do it:

jh.transform (5.0 KB)

There may be way with less transforms (a challenge to anyone else reading this!).

Adding back the header rows can be done with stack. I didn’t try to change the date format.

1 Like

It is on the wishlist to add more options to Concat Rows to make this sort of reshaping easier.

1 Like

Hi Andy

Thanks. This is exactly what I needed.
And I learned something from looking at your solution about EDT.

I have now looked a bit closer.
It seems rows is removed when Date and Time is the same.

Probably because of this:

Some days the same employee will log the same amount of hours multiple times.

Do you see any way to avoid the removal of these?

Ah, ok. I had to do a slight tweak to add a unique ID to each original record. I think it is right now.

jh2.transform (6.0 KB)

1 Like

Works perfectly now. Thanks a lot.

1 Like

Please see the improved Concat Rows transform (item 1) in this snapshot release:

Please le me know if it works ok for you.

I like how you remember I had this issue.

With the change to Concat Rows I can simplify the transform a lot.

See this screenshot:

1 Like

Glad that helps you.

1 Like