powerbi - 超过 24 小时时将列类型转换为时间

Question

我正在尝试在 Power Bi 中将列的格式从文本更改为时间：

当我将格式更改为时间时，超过 24 小时的任何内容都会显示为错误：

有谁知道如何解决这个问题？

标签： powerbi

Answer 1

您可以根据您的目的在高级编辑器代码下方尝试此操作。

条件像 (73:30) 这样的值需要一个分隔符:用于此代码。如果使用不同的分隔符，您还可以调整代码。

输出将在 dd:hh:mm:ss

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjSwMjBQitWJVjKGs8yNrYyBrFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Time", "Time - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Time - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Time - Copy.1", "Time - Copy.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Time", type text}, {"Time - Copy.1", Int64.Type}, {"Time - Copy.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Time - Copy.1", "hour"}, {"Time - Copy.2", "minutes"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Second", each ([hour]*60*60) + ([minutes]*60)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each #duration(0,0,0,[Second])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type duration}})
in
    #"Changed Type1"

输入-

输出-