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.
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êsCovering 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.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |