1

I have some data in an EXCEL TABLE which has total hours worked and actual work hours and which resembles the Duration in the form of %s as per the below-mentioned screenshot:

enter image description here

Now, I would use this Excel file in POWER BI and showcase the same duration and %s. But unfortunately, In PowerBI, the data type is being considered as Text, and hence unable to convert them to the formats as per Excel file. If I try to change it to Date or Date/Time, Decimal, or Duration, it is throwing an error. I need to perform calculations like the Sum of all the Durations etc.,\

Following is one type of error when I converted to Duration Data Type, likewise, it is showing an error for all the data types I tried.

enter image description here

Appreciate your help, thank you!

mrk777
  • 117
  • 1
  • 12
  • Refer this query, there is a solution posted: [More than 24 hr format issue in Power Query](https://stackoverflow.com/questions/68381588/more-than-24-hr-format-issue-in-power-query) – Mayukh Bhattacharya Apr 07 '23 at 17:51
  • One more: [Power Query: Parsing HH:MM:SS durations that are greater than 24 hours](https://stackoverflow.com/questions/38907024/power-query-parsing-hhmmss-durations-that-are-greater-than-24-hours) – Mayukh Bhattacharya Apr 07 '23 at 17:53

2 Answers2

1

The duration literal can have only a maximum value of 23 for the hours component. Otherwise you have to create that yourself.

You could add a custom column:
you will need to change the column names in the formulas to suit your actual data

enter image description here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2tDIytTI2VorViVYyMbQyMLYyNAFzDI0srEzMrQwMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each 
      let 
        Split = List.Reverse(List.Transform(Text.Split([Time],":"),each Number.From(_))),
        sec = Split{0},
        min = Split{1},
        hrs = Number.Mod(Split{2},24),
        dys = if List.Count(Split)=4 
                then Split{3} 
                else Number.IntegerDivide(Split{2},24)
      in  
        #duration(dys,hrs,min,sec), type duration)
in
    #"Added Custom"

or you could change it in place without adding an extra column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ2tDIytTI2VorViVYyMbQyMLYyNAFzDI0srEzMrQwMlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Transform to Duration" = Table.TransformColumns(Source, {"Time", (c)=> 
      let 
        Split = List.Reverse(List.Transform(Text.Split(c,":"),each Number.From(_))),
        sec = Split{0},
        min = Split{1},
        hrs = Number.Mod(Split{2},24),
        dys = if List.Count(Split)=4 
                then Split{3} 
                else Number.IntegerDivide(Split{2},24)
      in  
        #duration(dys,hrs,min,sec)}),
    #"Set Data Type" = Table.TransformColumnTypes(#"Transform to Duration", {"Time", type duration})

in
    #"Set Data Type"

Result
enter image description here

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • This is actually summing up to the totals in Power Query Editor itself. This is not what I require. I would like to convert the existing formats into proper Time Formats so that I can use that data in the Visualizations performing Aggregate Functions – mrk777 Apr 10 '23 at 09:02
  • @mrk777 I don't know what you mean. If you want to express the results as a duration in Power Query, this does that for each **individual** entry. Duration format in PQ is `d.hh:mm:ss`. Time format and Duration formats are different, and Time cannot have more than 23 hours (unlike Excel). It needs be expressed in days. But if having the result as total seconds is what you want instead, then your solution in your answer below will work. In Power BI, the result of my transform will be expressed as decimal days, as I don't believe PBI has a duration data type. – Ron Rosenfeld Apr 10 '23 at 10:33
  • Understood, but my question is can't we make the results to be displayed in each row instead of summing the data into 3 or 4 rows because each and every row represents the individual person's data? It can be in days or hours etc., – mrk777 Apr 11 '23 at 13:52
  • @mrk777 I still don't understand your issue. There is no summing of multiple rows happening (at least in the solutions I provided). Did you run the Query I provided? Why do you think that multiple rows are being added? – Ron Rosenfeld Apr 11 '23 at 18:21
  • I have tried the solution you have provided, it is actually showing only 3 or 4 rows of data – mrk777 Apr 12 '23 at 13:49
  • @mrk777 there should be one row of output for each row of input. I don't understand how you would be getting anything different from that. How did you try to adapt it to your actual data? – Ron Rosenfeld Apr 12 '23 at 14:29
  • Okay, I have 2 more datasets with the similar data which I need to import, I'll try the same code and let you know the status – mrk777 Apr 13 '23 at 08:59
  • @mrk777 I would not expect a different result. Please let me know exactly how you adapted my suggestion to your actual query. – Ron Rosenfeld Apr 13 '23 at 10:37
0

I found the solution to this.

Add the new column and update the following M code for that custom column:

Number.FromText ( 
    Text.BeforeDelimiter([duration],":") 
    ) * 60 * 60
+ Number.FromText ( 
    Text.BetweenDelimiters([duration], ":", ":" ) 
    ) * 60
+ Number.FromText ( 
    Text.AfterDelimiter([duration], ":", {0, RelativePosition.FromEnd})  
    )

For more details please have a look into this link: https://community.powerbi.com/t5/Power-Query/Convert-to-Time-Format-in-the-Power-Query-Editor-in-Power-BI/m-p/3178431#M102141

Please correct me If I'm going wrong, thank you!

mrk777
  • 117
  • 1
  • 12