Hi all,
I have a table which houses transaction data moving a candidate through the recruitment process. The problem is I have one date column which houses all the transactions. So candidate 1 could be listed multiple times moving from different steps. I’m struggling to be able to do any kind of date difference between stages with the current data setup. Is there a way I could pivot/unpivot or manipulate the date in Power Query so I could easily do a date difference of the stages for candidates 1-4 as they pass through the recruitment cycle?
date column example.xlsx (8.9 KB)
Hi @kurzashane,
You can use this measure for days:
_Days =
VAR _tb =
SUMMARIZE(
Sheet1,
Sheet1[candidate_id],
Sheet1[date],
“Days”,
VAR _date = SELECTEDVALUE(Sheet1[date])
VAR _toStage = SELECTEDVALUE(Sheet1[from_stage])
VAR _previousDate = CALCULATE(MAX(Sheet1[date]), FILTER(ALLEXCEPT(Sheet1, Sheet1[candidate_id]), Sheet1[date] < _date && Sheet1[to_stage] = _toStage))
RETURN DATEDIFF(_previousDate, _date, DAY) + 0
)
RETURN SUMX(_tb, [Days])
date column example.pbix (47.9 KB)
Is this possible to do in Power Query?
Hi @kurzashane,
Yes, it’s possible. See this m code:
let
Source = Excel.Workbook(File.Contents(“D:\Downloads\date column example.xlsx”), null, true),
Sheet1_Sheet = Source{[Item=“Sheet1”,Kind=“Sheet”]}[Data],
#“Promoted Headers” = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#“Changed Type” = Table.TransformColumnTypes(#“Promoted Headers”,{{“candidate_id”, Int64.Type}, {“user_id”, Int64.Type}, {“from_stage”, type text}, {“to_stage”, type text}, {“date”, type date}}),
#“Added Custom” = Table.AddColumn(#“Changed Type”, “Custom”, each let
_candidateID = [candidate_id],
_toStage = [from_stage],
_date = [date],
_nextDate = Table.SelectRows(#“Changed Type”,
each
[candidate_id] = _candidateID and
[to_stage] = [to_stage] and
[date] > _date) in
if Table.IsEmpty(_nextDate) then null else
Duration.Days(_nextDate[date]{0} - _date))
in
#“Added Custom”
1 Like
Hello again,
Would it be possible to also create a calculated column that shows the running total the candidate spent going through that HR process? So that we could see a cumulative total by candidates ID on a specific job? So the running total count would reset with a new candidate and job id?
1 Like
