Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone, could someone check the below file and advise how could I deal with these entries in the power query? It is read there with the data type of any, but apparently they're text, I cannot convert to duration as the hours are more than 24 hours so I get an error, I cannot convert to decimel or date and time, I cannot multiply by 1, I cannot split and column into hours, minutes and seconds and work on combining them back with a custom column because of the >24 hours thing, I'm stuck with no solution in mind further than I've mentioned that I tried, any insights please?
Solved! Go to Solution.
Table.AddColumn(Table1_Table, "toduration", each let d=Text.Split([Duration],":") in #duration(0,Number.FromText(d{0}),Number.FromText(d{1}),Number.FromText(d{2})))
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Jcy5DcAwDATBXhg74G/pWiHYfxuyqXCAxVaRBmSDX+qnKBhi4ByoQ38PfCMcpjcTWEJjkA43xLoI8PqG1H0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Duration", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Duration.1", "Duration.2", "Duration.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Duration.1", type number}, {"Duration.2", type number}, {"Duration.3", type number}}),
#"Inserted Integer-Division" = Table.AddColumn(#"Changed Type", "Days", each Number.IntegerDivide([Duration.1], 24), Int64.Type),
#"Inserted Division" = Table.AddColumn(#"Inserted Integer-Division", "Decimal", each ([Duration.1] / 24) , type number),
#"Inserted Subtraction" = Table.AddColumn(#"Inserted Division", "Hours", each [Decimal] - [Days], type number),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Decimal"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Hours", type duration}}),
#"Calculated Total Hours" = Table.TransformColumns(#"Changed Type1",{{"Hours", Duration.TotalHours, type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Calculated Total Hours",{{"Duration.2", "Minutes"}, {"Duration.3", "Seconds"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "Duration", each #duration([Days], [Hours], [Minutes], [Seconds]), type duration)
in
#"Added Custom"