Conditional column for # of days employed

[jcastr02]

I have a column that shows the # of days the team member has been employed.  Id like to create a conditional column that categorizes as follows      

0-30 days = <30 days

31-90 days = 1-3 months

91-365 = 3-12 months

366-1094=1-3 years

1095+=3 years+

When I go to create conditional column I am having difficulty putting a "between" number.  Any recommendations is appreciated.  Id like to build in PQ so that I have the column visible to transform data with.  Thank you.  

[HotChilli · Accepted solution]

The conditional column screen doesn't have the features for complex 'and' conditions.  If you edit the code slightly in the formula bar or Advanced Editor, it will allow you to use the Custom Column dialog to write the full statement.

So change the code in formula bar to be similar to this (just an example):

if [a] > 12 and [a] < 15 then "12-15" else "<12"

 Hit the tick  on the left of the bar to save it.

Then go to the Applied Steps and edit the query step.  It should open the Custom Column dialog and you can write the statement there.

-----

You can, of course, start fresh with the Add New Column -> custom column

[jcastr02]

@HotChilli This worked! You are awesome thanks!

[HotChilli]

what have you got so far?

[jcastr02]

@HotChilli   see below.

[jennratten]

Hello - here are the steps that I recommend following...

• Create a custom table containing the minimum number of months to qualify for each label as well as the label you'd like to apply.
• Add a new column to your data table number of months elapsed between the two dates.  I have created a function for this.
• Normalize the months elapsed column in your data table by replacing values less than the specific min and greater than the specified max.
• Merge the data table with the custom table with the key columns being the months elapsed from the data table and min months from the custom table.
• Finally, expand to get the labels that should be applied to each row.

Custom Function to calculate the date difference in months:

// ****************************************************************************************************************/
//   PURPOSE
//   - Calculate the difference between two dates in months
// ****************************************************************************************************************/

let
    fn = ( DataTable as table, StartDateColumn as text, EndDateColumn as text, NewColumnName as text ) as table =>

    let
        
        date_diff = Table.AddColumn(
                 // starting table
                DataTable,
                // name of new column to be created
                NewColumnName, 
                each 
                    // declare inline variables
                    let 
                        // calculate the compelete years elapsed
                        DateDiffInYears = 
                            Date.Year ( Record.Field ( _, EndDateColumn ) ) - Date.Year ( Record.Field ( _, StartDateColumn ) ),
                        // calculate the remaining months
                        MonthsRemaining = 
                            Date.Month ( Record.Field ( _, EndDateColumn ) ) - Date.Month ( Record.Field ( _, StartDateColumn ) ),
                        // calculate the total number of months
                        DateDiffInMonths = 
                            ( DateDiffInYears * 12 ) + MonthsRemaining
                    in
                        DateDiffInMonths,
                        Int64.Type
            )
    in
        date_diff
in
    fn

Sample Data Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYzBDcAgDAN34Y0U2xFdBrH/Gg0FJKL+zj7LvRcaTRBLDdTCUVOvg7N3Y4sEhHhM7WeE78p5f/l9tlfT7ERkM14=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [startDate = _t, endDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"startDate", type date}, {"endDate", type date}})
in
    #"Changed Type"

Sample Custom Date Range Dim

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdI7CgIxFEbhrYS0jnBz/3kuwgVITDGoYKXgo5jdGxxBi9N+3YGTc7TYxMPLTMcgC6d5ecTS5Jgqp7ANCrvb9XlZ0QlF2FZUxeT/2qH2qAPqiDqhJmNOzM78y9uf5/sXW8KOsCccCEfCCdCNMBE6IRU5FTkVORU5FTkVORWJikRFoiJRkahIVCQq0vrfB8ImlvIG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [MinMonths = _t, Label = _t]),
    SetTypes = Table.TransformColumnTypes(Source,{{"MinMonths", Int64.Type}})
in
    SetTypes

Result

let
    Source = fnDateDifferenceInMonths(SampleTable, "startDate", "endDate", "DateDiffInMonths"),
    LowestMinMonthsInDim = List.Min(CustomDateRangeDim[MinMonths]),
    HighestMinMonthsInDim = List.Max(CustomDateRangeDim[MinMonths]),
    NormalizeMonths = Table.ReplaceValue(
        Source, 
        each [DateDiffInMonths] , 
        each 
            // if the months elapsed is less than the lowest min in the dim table
            if [DateDiffInMonths] < LowestMinMonthsInDim 
            // replace with the lowest min
            then LowestMinMonthsInDim
            else 
                // if the months elapsed is greater than the highest min in the dim table
                if [DateDiffInMonths] > HighestMinMonthsInDim then 
                // replace with the highest min
                HighestMinMonthsInDim 
                // otherwise perform no replacement
                else [DateDiffInMonths],
                Replacer.ReplaceValue,
                {"DateDiffInMonths"}
    ),
    // merge with the custom date dim
    merge = Table.NestedJoin(NormalizeMonths, {"DateDiffInMonths"}, CustomDateRangeDim, {"MinMonths"}, "Table", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(merge, "Table", {"Label"})
in
    #"Expanded Table"

---

@jcastr02 wrote:

@HotChilli   see below.

---

Then