Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Justas4478
Responsive Resident
Responsive Resident

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.

Justas4478_0-1695800895192.png

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

1 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

View solution in original post

10 REPLIES 10
JoeBarry
Solution Sage
Solution Sage

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

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

Justas4478_0-1695809583056.png

 

Should the result be 9:00 here?

@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.

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

@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?

Justas4478_0-1695812552666.png

 

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. 

 

 

 

@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_0-1695814438445.png

 

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

Justas4478_0-1695811074872.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors