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.
Solved! Go to 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
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
what have you got so far?
Hello - here are the steps that I recommend following...
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