Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jcastr02
Post Prodigy
Post Prodigy

Conditional column for # of days employed

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.  

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

5 REPLIES 5
HotChilli
Super User
Super User

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

@HotChilli This worked! You are awesome thanks!

HotChilli
Super User
Super User

what have you got so far?

@HotChilli   see below.conditional .png

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"

 

 

jennratten_2-1624641796181.png

 

 

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

 

 

jennratten_1-1624641768973.png

 

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"

 

 

jennratten_4-1624642219583.png

 

 

 

 

 


@jcastr02 wrote:

@HotChilli   see below.conditional .png


 

Then 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors