cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DataFormat.Error converting number to duration - am I missing something?

Hi there,

 

I have a column in my query editor which is a whole number. The source data is an amount of elapsed time in seconds.

 

I want this column to show days + hours, rather than seconds. My attempts so far have been:

 

Change Type>Duration.

 

But this produces a DataFormat.Error for any numbers above a certain threshold. eg., It can convert 10411192 but not 11842910.

 

Because of this my next two steps will only produce results for 8 digit numbers below a certain threshold.

 

These steps are: Duration>Divide value 86400

 

followed by: Transform>Total Days.

 

Is there something I'm missing on the above? Better yet, is there a smarter way to convert seconds to days + hours in the Query Editor?

 

Thanks in advance,


Dylan

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: DataFormat.Error converting number to duration - am I missing something?

Hi @dyoung,

 

Based on my test, you should be able to simply use the formula below to add a new custom to convert seconds to durations. Smiley Happy

= #duration(0,0,0,[Column1])

r3.PNG

 

Regards

View solution in original post

2 REPLIES 2
Highlighted
Microsoft
Microsoft

Re: DataFormat.Error converting number to duration - am I missing something?

Hi @dyoung,

 

Based on my test, you should be able to simply use the formula below to add a new custom to convert seconds to durations. Smiley Happy

= #duration(0,0,0,[Column1])

r3.PNG

 

Regards

View solution in original post

Highlighted
Frequent Visitor

Re: DataFormat.Error converting number to duration - am I missing something?

Thanks so much for your reply, v-ljerr-msft.

 

Have gone through my data and your formula works a treat.

 

After my original post I'd come up with a different solution based off an old excel forumla I used:

 

= [Column1]/86400 

 

Your forumla produces the exact same result which is great to know. I can sleep easy.

 

Thanks again for helping out.

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors