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
acrscotland
New Member

Duration conversion issue (CVS)

Hi all,

 

I'm struggling with what I feel should be a simple issue!  I have a CSV file coming from some time tracking software I use.  The format is HH: MM: SS and represents the duration of a task. When imported into Power BI, it looks like this (format of the column is 'Time').  I then parse this and convert myself into a decimal for the purposes of charting:

2017-02-10 15_54_39.png

 

As an aside, converting to Duration doesn't work and triggers the following error:

2017-02-10 15_57_17.png

Anyway, all is well until I have a duration >24 hours.  Then it get's upset:

2017-02-10 15_59_50-Finance - Query Editor.png

I've tried converting to Text but then it tries to convert it to an actual Time of Day and that's no good.  Any idea how I can handle this situation?

 

Thanks in advance!!

1 ACCEPTED SOLUTION

I just made a small working sample.

 

First I duplicated the Duration column. Then I used the "Split Column" function. That created 3 columns containg hours, minutes and seconds.

 

02.PNG

03.PNG

 

04.PNG

 

 

 

Hope this works with your data, too!

 

JJ

View solution in original post

9 REPLIES 9
DoubleJ
Solution Supplier
Solution Supplier

How about you

- convert the values to a string

- split the string value in 3 columns (hours, minutes, seconds) (split by the ":" character)

- add a calculated column that computes the duration with these 3 values?

 

JJ

Thanks for such a quick suggestion.  This is actually what I tried first but unfortunately, when converting the column to 'Text' (I'm doing this within the Query Editor, it converts to the following, which I can't parse since it's no longer a duration (these are the same values as I pasted above so as an example, 00:30:00 (30 mins) is converted to 12:30 AM:

 

2017-02-10 16_14_40.png

 

Is there a different way to convert that you had in mind?  It doesn't work once the import has completed since any hours >24 error out during import.

 

 

I just made a small working sample.

 

First I duplicated the Duration column. Then I used the "Split Column" function. That created 3 columns containg hours, minutes and seconds.

 

02.PNG

03.PNG

 

04.PNG

 

 

 

Hope this works with your data, too!

 

JJ

This is perfect, thanks!!  I realized that somewhere along the line I can converted the column to something else along the way and that screwed me up.  This solution has me up and running - thanks!

you're welcome, glad I could help 🙂

Sean
Community Champion
Community Champion

Alternatively Smiley Happy

Add Column tab => select column => Time button => Hour

repeat 2 more times  => select column => Time button => Minute and then again for a Second column

 

QE - Add Time Columns.png

 

DoubleJ
Solution Supplier
Solution Supplier

nice solution!

Sean
Community Champion
Community Champion

When I have a csv fle containing:
Input
02:00:00
03:30:20
40:00:00

 

Then the following code works with me:

 

let
    Source = Csv.Document(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Durations from csv.csv"),[Delimiter=",", Columns=1, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
// Split text at ":" (list of 3 items) and convert to number:
    Split = Table.AddColumn(#"Promoted Headers", "d", each List.Transform(Text.Split([Input],":"), Number.From)),

// Get duration from the set 3 numbers
    Duration = Table.AddColumn(Split, "Duration", each #duration(0,[d]{0},[d]{1},[d]{2}), type duration)
in
    Duration
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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