Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.