Data in days and hours in one column.

[GOLEARY735]

Hi, 

I have an issue where a data set pulls in any value less than 1 working day (8 hours) in hours rather than days (Last row in the below image).

I can remove the text 'hrs' and 'mins' in my custom column named 'Text.Remove', but then I'm left with data where I can't tell if it's in hours or days.

I need to sum up total duration in days in my dashboard. I am hoping that someone knows a solution to this please? I can't find one by searching the internet so far. 

Thanks, 

Grace

[Anonymous · Accepted solution]

Hi, @GOLEARY735 

You can try the following methods.

Measure = CALCULATE(SUM('Table'[Text.Remove]),FILTER(ALL('Table'),[Unit of Time]="day"))&"day"

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@GOLEARY735 , Try using below mentioned M code

let
// Replace 'YourTable' with your actual table name
Source = YourTable,

// Step 1: Remove 'hrs' and 'mins'
CleanedDuration = Table.AddColumn(Source, "CleanedDuration", each Text.Remove([YourDurationColumn], {"hrs", "mins"})),

// Step 2: Convert hours to days
DurationInDays = Table.AddColumn(CleanedDuration, "DurationInDays", each if Text.Contains([YourDurationColumn], "hrs") then Number.FromText([CleanedDuration]) / 8 else Number.FromText([CleanedDuration])),

// Step 3: Sum up the total duration in days
TotalDurationInDays = List.Sum(DurationInDays[DurationInDays])
in
TotalDurationInDays