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.
I am trying to change the following table (currently have over 8,000 rows) to a Data Type: Duration. Curently is in Data Type: Text. Need to sum all the durations in a Power BI Dashboard, but when editing or changing the Data Type on Power Query it gives me an Error Output.
Time |
4 hours 10 minutes |
1 hour 43 minutes |
53 minutes |
1 day 20 minutes |
3 days 7 hours 33 minutes |
8 hours |
7 hours 1 minute |
Need Help
THANKS
Solved! Go to Solution.
Hi @wtmartinoms
Try this script
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHIyC8tKlYwNFDIzcwrLUktVorViVYyBAsrmBijiJoaoylKSaxUMELVaQwSLFYwh5prjKrFAiIMZsOUGEJVKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let
lst = Text.Split( [Time], " " ),
Value = List.Transform( List.Alternate( lst, 1, 1, 1 ), Number.FromText ),
Name = List.Transform( List.Alternate( lst, 1, 1 ), each Text.Start( _, 1 ) ),
rec = Record.FromList( Value, Name )
in
#duration(
try rec[d] otherwise 0,
try rec[h] otherwise 0,
try rec[m] otherwise 0, 0
), type duration
)
in
#"Added Custom"
Hi @wtmartinoms
Try this script
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHIyC8tKlYwNFDIzcwrLUktVorViVYyBAsrmBijiJoaoylKSaxUMELVaQwSLFYwh5prjKrFAiIMZsOUGEJVKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Time = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let
lst = Text.Split( [Time], " " ),
Value = List.Transform( List.Alternate( lst, 1, 1, 1 ), Number.FromText ),
Name = List.Transform( List.Alternate( lst, 1, 1 ), each Text.Start( _, 1 ) ),
rec = Record.FromList( Value, Name )
in
#duration(
try rec[d] otherwise 0,
try rec[h] otherwise 0,
try rec[m] otherwise 0, 0
), type duration
)
in
#"Added Custom"
This would help. Currently is bringing me the user table i used as example.
Trying to change it to the colunm I need to change (who holds over 8,000 rows)
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.