Question about time since hire

[CzechCzar]

Hello,

I have used these forums extensively in the past to research, and am now hoping you all can help me with PowerQuery.

I have a text field "Time Since Hire". It essentially lists the time since hire in this format:

12 year(s), 5 month(s), 1 day(s)

I have extracted the numbers into their own columns using delimeters and trim. So, I now have a separate column for Years, Months, and days, since hire.

How would I combine the three of these into one year value? So, the above three columns would be interpreted as a little less than 12.5.

Any help is much appreciated!!!

[CzechCzar]

Thank you, that worked like a charm!!

[foodd]

Fantastic, and thank you.  Now., If I may ask, would you please add Kudos as well to the Solution, as it helps other folks find a solution faster.

[foodd · Accepted solution]

In the power query editor create a custom column using the following:

= Table.AddColumn(#"Added Custom1", "Total Years", each [Years] + [Months]/12 + [Days]/365)

let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText("i45WMjRS0lEyBWJDpdhYAA==", BinaryEncoding.Base64),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Years = _t, Months = _t, Days = _t]
),
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Years", Int64.Type}, {"Months", Int64.Type}, {"Days", Int64.Type}}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Total Years",
each [Years] + [Months] / 12 + [Days] / 365
)
in
#"Added Custom"