Forum Discussion
Question about time since hire
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!!!
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"
3 Replies
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"Thank you, that worked like a charm!!
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.
