Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
I have a CSV fil with a duration column in HH:MM:SS fotmat. When I try to change the column type to 'Duartion' one value gives an error, because the value is larger than 24 hours and the standard duration format is HH:MM:SS (and does not contains days). Does someone know how to 'enlarge' the duration column and add 'days', so the format will be DD:HH:MM:SS (al hours >24 have to be converted to a day)?
Many thanks in advance.
Solved! Go to Solution.
Hi @Josef ,
No need to go to the advance editor. Go to the last step of yur query add a custom column and copy paste the code that I send in the previous post:
Be aware that twhere there is [Duration] you should place the name of the column you want to convert I have place Duration based on the image you shared.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Josef ,
This as to do with the parsing of the hours and time functions on the query editor
You need to add a column with the following code:
let
DurationFromYourText = (text as text) as duration => let
Split = Text.Split(text, ":"),
AsNumbers = List.Transform(Split, Number.FromText),
Duration = #duration(0, AsNumbers{0}, AsNumbers{1}, AsNumbers{2})
in
Duration,
Invoked = DurationFromYourText([Duration])
in
Invoked
Should work as expected.
This formula comes from the post:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix thanks alot for your reply.
Do you mean I have to add this code in the Advanced Power Query Editor?
I tried to insert it in the current steps, but I'm not able to get it right.
At the moment it looks like this:
Could you please help me out to inset the code in the right way? It would be appreciated a lot.
Hi @Josef ,
No need to go to the advance editor. Go to the last step of yur query add a custom column and copy paste the code that I send in the previous post:
Be aware that twhere there is [Duration] you should place the name of the column you want to convert I have place Duration based on the image you shared.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
93 | |
83 | |
77 | |
71 | |
65 |
User | Count |
---|---|
114 | |
101 | |
96 | |
65 | |
60 |