Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
As shown above the duration 85h 14m 16s is represented in the background as 1/3/1900 1:14:16 PM.
PowerQuery captures the 1/3/1900 1:14:16 PM value, now I'm having trouble converting it back to 85H 14m 16s.
Any ideas?
Hi @MarcelBeug, have you done anything like this?
Thank you.
Solved! Go to Solution.
Thanks for that note, I'll keep that in mind. Good thing I didn't convert it immediately I converted the data while it is in an unknow datatype state(text)
=(Date.Day([#"MyData"]) * 8640) + (Time.Hour([#"MyData"]) * 360) + (Time.Minute([#"MyData"]) * 60) + Time.Second([#"MyData"])
I basically converted to seconds!
Alternatively you can use:
= Duration.TotalSeconds([MyData] - #datetime(1899,12,31,0,0,0))
Notice that you will get wrong results from your solution for durations > 31 days, as the day number will become 1 for February 1, 1900. This is also the case when durations from Power Query are loaded into an Excel table.
You may be interested in this video:
DAX Mode ( In this mode you can agregate for any dimension)
QtSegundos = SUM([Seconds])
Time= VAR Horas = INT([QtSegundos]/3600) VAR Minutos = INT(([QtSegundos] - (Horas * 3600))/60) VAR Segundos = MOD([QtSegundos];60) RETURN Horas&":"&FORMAT(Minutos;"00")&":"&FORMAT(Segundos;"00")
Okay, I already have an idea, 1/1/1900 actually represents 24:00:00 so I can start from there.
Be careful: Excel and Power Query have different zero bases for date/times.
In Excel, 0 represents 31/12/1899 0:00; in documentation also refered to as 1900/1/0 (zero January 1900).
In Power Query, 0 represents 30/12/1899 0:00.
Why?
Because Excel regards 1900 as a leap year. so 2/29/1900 is day 60 according to Excel and 3/1/1900 is day 61.
In Power Query this is corrected (1900 is not a leap year): all dates from 3/1/1900 (March 1) have the same number in Excel and Power Query, so counting back to zero takes you to 12/30/1899 0:00 as zero base in Power Query.
If you import Excel 1/3/1900 1:14:16 PM as datetime in Power Query, it becomes 1/2/1900 1:14:16 PM.
Thanks for that note, I'll keep that in mind. Good thing I didn't convert it immediately I converted the data while it is in an unknow datatype state(text)
=(Date.Day([#"MyData"]) * 8640) + (Time.Hour([#"MyData"]) * 360) + (Time.Minute([#"MyData"]) * 60) + Time.Second([#"MyData"])
I basically converted to seconds!
Alternatively you can use:
= Duration.TotalSeconds([MyData] - #datetime(1899,12,31,0,0,0))
Notice that you will get wrong results from your solution for durations > 31 days, as the day number will become 1 for February 1, 1900. This is also the case when durations from Power Query are loaded into an Excel table.
You may be interested in this video:
I am confident that there will be no duration that will go beyond 28 days but I will definitely take your advise plus this one is better since it's OOTB!
I will have to change my solution then... Thank you, thank you!!!
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
62 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |