How to Convert Time Fields from APIs for use in Power BI

Many APIs in L2L have Julian Time listed for time fields. Often, our customers want to convert that to a number that makes sense. Below is one example and the solution. You should be able to use that solution for all Julian Dates you find.

/api/1.0/dispatches has a field called “timedispatched” that is returning the initial time dispatched as a numerical value.

This will not convert to a time base using excel power query or power bi.

To get the actual time a custom column must be inserted using the M language formula:

each Text.PadStart(Text.From([CallFunction.timedispatched]),6,"0")

This formula adds the leading zeros based on character count.

Once that is complete it can be converted to a time base to show the actual time the tech was initially dispatched.