Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
plz could you help me to convert this: 00:06:30 to seconds in power bi
Thank you
Solved! Go to Solution.
Hi @ayoubb
for average with @v-alq-msft solution you can try AVERAGE([TotalSecond column])
or
AVERAGEX(Table, HOUR([Time])*3600+MINUTE([Time])*60+SECOND([Time]) )
Hi, @ayoubb
You mau try to create a custom in 'Query Editor' or create a calculated column/measure in Power BI Desktop. I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
Query Editor:
You may create a custom column as below.
Here is the code in 'Advanced Editor'.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjCwMjCzMjZQitWBcCyQOZZWhqZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}}),
Custom1 = Table.AddColumn(#"Changed Type","TotalSecond",each Time.Hour([Time])*3600+Time.Minute([Time])*60+Time.Second([Time])
)
in
Custom1
Calculated column:
TotalSecond Column = HOUR([Time])*3600+MINUTE([Time])*60+SECOND([Time])
Or
Measure:
TotalSecond Measure =
var _time = SELECTEDVALUE('Table'[Time])
return
HOUR(_time)*3600+MINUTE(_time)*60+SECOND(_time)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much for your help,
if i want to calculate the average of this colonne after using this formula : TotalSecond column = HOUR([Time])*3600+MINUTE([Time])*60+SECOND ([Time])
is it gona be:
Average in seconds = Average ([Time])????
Hi @ayoubb
for average with @v-alq-msft solution you can try AVERAGE([TotalSecond column])
or
AVERAGEX(Table, HOUR([Time])*3600+MINUTE([Time])*60+SECOND([Time]) )
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |