cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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.

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
Solution Sage

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 10
Solution Sage

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

Responsive Resident

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

Solution Sage

Should the result be 9:00 here?

Responsive Resident

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

Solution Sage

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

Responsive Resident

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

Solution Sage

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.

Responsive Resident

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

Responsive Resident

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

Solution Sage

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors