CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Mark Tait
Mark Tait

Reputation: 643

Convert D:HH:MM:SS (text) to Days (dec) in PowerBI

When adding a custom column in Power BI, is there a way of making that column calculated from a text D:HH:MM:SS column, to a decimal "Days" column?

The raw data I have in the CSV, is in text format (first column), should end up having a calculated column of Days:

Text to Days

If there's not a calculation, is it possible to parse the text of the first column into D, H, M, S and then calculate Days from the resulting text?

Thanks for any help,

Mark

Upvotes: 0

Views: 1038

Answers (1)

Andrey Nikolov
Andrey Nikolov

Reputation: 13440

One way to do this is to split the column by delimiter (colon in this case)

enter image description here

to get separate columns for days, hours, minutes and seconds:

enter image description here

And then add a custom column using this formula:

[Text.1] + ([Text.2]/24) + ([Text.3]/1440) + ([Text.4]/86400)

enter image description here

to calculate the decimal duration:

enter image description here

Of course, you can delete these columns later if you don't need them, or you can rename them to something more meaningful if you decide to keep them.

Upvotes: 1

Related Questions