type conversion error question RRS feed

  • Question

  • When parsing Duration out of a json string, I am getting this error:

    Expression.Error: We couldn't parse the Duration literal.

    Is it necessary to convert first to decimal number and then to duration?
    Saturday, October 17, 2015 6:38 PM


All replies

  • I found that it was necessary to first convert to number and then to duration. Why can't it convert from string to duration?

    Also, now that I have a duration column, I move the data model to Power BI. But Power BI doesn't have a duration data type so it just shows the number. Why wouldn't Power BI have the same data types as M?

    Saturday, October 17, 2015 6:48 PM
  • Just found this feature request:

    But back to duration. Is Duration.FromText() broken? I do


    and still get the error

    Expression.Error: We couldn't parse the Duration literal.

    That probably explains why Table.TransformColumnTypes() also can't do it.

    Saturday, October 17, 2015 7:20 PM
  • I tried this to do it all in one statement:

    TableWithDuration = Table.AddColumn(TableWithShare, "Avg. Length of Visit", each Duration.ToText(Duration.From(Number.FromText([Avg. Session Duration])))),

    but instead of getting "00:02:04" I am getting "0.001445" which is the original decimal value.

    Is there any way to get a formatted duration string with Power Query?

    Thanks, Chris

    Saturday, October 17, 2015 7:36 PM
  • This did work to give column formatted as "hh:mm:ss". Wishing it were easier. Anyone got a more direct patttern?

    #"Changed Type" = Table.TransformColumnTypes(#"Expand Column1",{{"Avg. Session Duration", type number}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type",{{"Avg. Session Duration", type duration}}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"Avg. Session Duration", type text}}),
    TableWithFmtDuration = Table.AddColumn(#"Changed Type3", "Avg. Length of Visit", each Text.Start([Avg. Session Duration],8)),

    Saturday, October 17, 2015 7:49 PM
  • Duration.FromText expects the format "DD.HH:MM:SS". So yes, to go from a decimal text value to a duration you need to first convert to a number and then to a duration.


    = let seconds = "90.0" in Duration.From(Number.From(seconds)/86400)


    Tuesday, October 20, 2015 5:29 PM