Combining multiple columns to add time together

[Justas4478]

Hello, I have multiple columns that have work time per day in them. These columns represent different pay rate that is payed for the work time. There is 20 rate columns but data really show mainly in first 6.

What I need to do is combine these columns in to one column since each row is different day. Majority of the time there is just one rate column filled per day, but some times there can be couple of them and thats where I dont know how to combine them since it failes to add times together. 

I attach screenshot showing some of the data.

As you can see in row 84 -85 there is only one shift recorded per row but rows 92 and 119 have extra time.

My expected results would be to combine all 20 row columns and expected result for row 92 would be 09:00 and row 119 would be 08:15.

Anyone could help to solve this problem?

Thanks

[JoeBarry]

Hi @Justas4478 

If you want to view the data correctly and do measures on the data, then you need to format your data differently. 

Convert the date column from datetime to date. Highlight all the Rate columns and right click and unpivot all columns. You will be left with the Date and a new Attribute and Values column. Change Attribute column name to RateType and Values to Rate. Filter out a null or blank values from the Rate column.

When you load the data into the report, you can make various measures because the data is correctly formatted.

Thanks

Joe

If this post helps, then please Accept it as the solution

[Justas4478]

@JoeBarry It looks better, but how do I combine different rates of same day in to one?

[JoeBarry]

Should the result be 9:00 here?

[Justas4478]

@JoeBarry If we dont look at it as different rates then yes result would be 09:00 

Since 01:00 is overtime and still needs to be included in total time for the day.

[JoeBarry · Accepted solution]

ok, create a new column. and add this code 

each Duration.FromText([Rate]) / #duration(0, 0, 1, 0))

This converts the hours into minutes. Delete the Rate column. Highlight the date column, go to the Ribbion > Home > Group by Operation is Sum and choose the new minutes column to do the sum.

This will group all the minutes in to one day. You can then divide by 60 to get the hours worked.

Thanks

Joe

[Justas4478]

@JoeBarry I did Group by and Sum and it worked, but it removed all other columns excelp date and new column that resulted from group by. 

Is there a way to bring other columns back while keeping the column that resulted from group by?

[JoeBarry]

So you still need the breakdown of where the hours went? 

You could before unpivoting, make duplicates of the Rate columns and then make sure they are highlighted along with the date when grouping. 

[Justas4478]

@JoeBarry I might try that, but what I am more need is other information. There were other columns like employee id, clock in and out columns and unique ID that is for everyhing that happens on that Date.

Do I just select those columns when I do group by so that they would not dissapear?

[Justas4478]

@JoeBarry I am getting this error when I try to use the code.

[JoeBarry]

sorry, i copied the code from the formula bar, please remove the each part. Also please filter out the blank values in the rate column beforehand