Forum Discussion

harryli's avatar
harryli
Icon for Helper II rankHelper II
4 years ago

how to get the index value in the string/datetime

As the above data is one DateTime value minus another DateTime value which is the DirectQuery mode. Now, I would like to transfer it to be only minutes value, but there is a situation like the...
Need Help
harryli's avatar
harryli
Icon for Helper II rankHelper II
3 years ago

Thanks for the reply, I know this method, but I am using "DirectQuery" mode. After I import the Duration function, the system show "switch to import mode". Therefore, I have to find the transfer method in "DirectQuery" mode. Anyway, thanks for the help.

 

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago

Power BI interprets time in units of days, so you might be able to just multiply by the number of minutes in a day (24 hr/day * 60 min/hr = 1440 min/day).

 

I'm not sure if the conversion to decimal will fold but it's worth a try.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwMjWxMjTUMzI2MFGK1QGKGFoZGVkZmEJFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [current = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"current", type time}}),
    ToDecimal = Table.TransformColumnTypes(#"Changed Type",{{"current", type number}}),
    #"Added Custom" = Table.AddColumn(ToDecimal, "Minutes", each [current]*24*60, type number)
in
    #"Added Custom"

  • harryli's avatar
    harryli
    Icon for Helper II rankHelper II
    3 years ago

    if changes the datatype will not available to DirectQuery mode.

    • harryli's avatar
      harryli
      Icon for Helper II rankHelper II
      3 years ago

      I mean your [current] column.