Forum Discussion
Combining multiple columns to add time together
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
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
10 Replies
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
Should the result be 9:00 here?
