Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Good evening Power BI community,
Hoping someone can help me with this challenge that I would like to proper and elegant solution for.
I am starting with data that looks like this:
Personnel no. | Personnel Name | Salary Band | Reason on Pay Record | Basic Pay Record Start Date |
123 | Jean-Luc Picard | 12 | Merit Review | 03/01/2019 |
123 | Jean-Luc Picard | 13 | Promotion | 03/01/2020 |
123 | Jean-Luc Picard | 13 | Other | 10/19/2020 |
123 | Jean-Luc Picard | 13 | Other | 11/06/2020 |
234 | Benjamin Sisco | 10 | Merit Review | 03/01/2019 |
234 | Benjamin Sisco | 11 | Merit Review | 03/01/2020 |
234 | Benjamin Sisco | 11 | Promotion | 06/25/2021 |
3434534 | Thomas Riker | 10 | Other | 01/01/2019 |
3434534 | Thomas Riker | 10 | Merit Review | 03/01/2019 |
3434534 | Thomas Riker | 10 | Other | 01/01/2020 |
3434534 | Thomas Riker | 10 | Merit Review | 03/01/2020 |
3434534 | Thomas Riker | 10 | Other | 01/01/2021 |
666667 | Travis Mayweather | 8 | Other | 02/04/2019 |
666667 | Travis Mayweather | 9 | Other | 11/03/2019 |
666667 | Travis Mayweather | 10 | Other | 03/01/2020 |
The records above are entries from pay record entries. The ask was to identify:
1. Where/when the actual promotions are happening. (a promo is an increase in salary band)
2. Which promotions were on cycle (happening in March) vs off cycle (any other month than March)
Notice the following:
- each employees records are laid out in chronological order. (See Basic Pay Record Start Date)
- the reason code is not consistent and therefore useless to try and harness... changes in salary band seem to happen for different reason codes
I would like to end up with something like this:
Personnel no. | Personnel Name | Salary Band | Reason on Pay Record | Basic Pay Record Start Date | PROMO STATUS | PROMO CYCLE |
123 | Jean-Luc Picard | 12 | Merit Review | 43525 | ||
123 | Jean-Luc Picard | 13 | Promotion | 43891 | Promotion | On Cycle |
123 | Jean-Luc Picard | 13 | Other | 44123 | ||
123 | Jean-Luc Picard | 13 | Other | 44141 | ||
234 | Benjamin Sisco | 10 | Merit Review | 43525 | ||
234 | Benjamin Sisco | 11 | Merit Review | 43891 | Promotion | On Cycle |
234 | Benjamin Sisco | 11 | Promotion | 44372 | ||
3434534 | Thomas Riker | 10 | Other | 43466 | ||
3434534 | Thomas Riker | 10 | Merit Review | 43525 | ||
3434534 | Thomas Riker | 10 | Other | 43831 | ||
3434534 | Thomas Riker | 10 | Merit Review | 43891 | ||
3434534 | Thomas Riker | 10 | Other | 44197 | ||
666667 | Travis Mayweather | 8 | Other | 43500 | ||
666667 | Travis Mayweather | 9 | Other | 43772 | ||
666667 | Travis Mayweather | 10 | Other | 44013 | Promotion | Off Cycle |
I was actually planning at one point to have columns for each year but it turns out that some people get more than one promotion in the same year so that would not work so well.
Appreciate any insight.
Thank you inadvance PBI Community
~Jano
Solved! Go to Solution.
Good morning Jakinta!
Thank you for this solution!... BTW... you are correct... I missed one PROMO scenario... you got it right.
I am having trouble putting the code into power query. I am putting it into power query in excel.. does that make a difference?
This is what my code looks like:
let
Source = Excel.CurrentWorkbook(){[Name="All_HR_Promos"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Personnel no.", Int64.Type}, {"Personnel Name", type text}, {"Salary Band", Int64.Type}, {"Reason on Pay Record", type text}, {"Year", Int64.Type}, {"Basic Pay Record Start Date", type datetime}, {"Basic Pay Record End Date", type datetime}, {"Current Position Title", type text}, {"Current Position #", Int64.Type}, {"Current Manager Name", type text}, {"Current Managing Position", type text}, {"Current Org Level 4", type text}, {"Current Org Level 5", type text}, {"Current Org Level 6", type text}, {"Current Org Level 7", type text}, {"Current Org Level 8", type text}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type", {"Salary Band"})
[#"Personnel no." = _t, #"Personnel Name" = _t, #"Salary Band" = _t, #"Reason on Pay Record" = _t, #"Basic Pay Record Start Date" = _t]),
Grouped = Table.Group(Source, {"Personnel no."}, {{"Gr", each let t=_ in Table.AddColumn( Table.AddColumn( Table.AddIndexColumn(t, "i",-1,1), "PROMO STATUS", each try if Number.From([#"Salary Band"]) > Number.From(t[#"Salary Band"]{[i]}) then "Promotion" else "" otherwise ""), "PROMO CYCLE", each if [#"PROMO STATUS"]="" then "" else if Date.Month(Date.From([#"Basic Pay Record Start Date"])) =3 then "On Cycle" else "Off Cycle" ), type table }}),
Removed = Table.RemoveColumns(Grouped,{"Personnel no."}),
FINAL = Table.ExpandTableColumn(Removed, "Gr",
List.RemoveItems (Table.ColumnNames(Removed[Gr]{0}), {"i"}))
in
FINAL
I am getting an error - Expression.SyntaxError: Token RightBracket expected.
It is showing that there is an error here:
[#"Personnel no." = _t, #"Personnel Name" = _t,
^
Please advise. Thank you!
@JanoLehocky Maybe:
PROMO STATUS Column =
VAR __Previous = MAXX(FILTER('Table',[Personnel no.] = EARLIER([Personnel no.] && [Basic Pay Record Start Date] < EARLIER([Basic Pay Record Start Date])),[Basic Pay Record Start Date])
VAR __PreviousBand = MAXX(FILTER('Table',[Personnel no.] = EARLIER([Personnel no.] && [Basic Pay Record Start Date] = __Pevious),[Salary Band])
RETURN
IF(__PreviousBand > [Salary Band],"Promotion",BLANK())
PROMO CYCLE =
IF(MONTH([Basic Pay Record Start])=3 && [PROMO STATUS] = "Promoted","On Cycle",BLANK())
@Greg_Deckler - Forgive me I am not entirely sure how to use your solution. Where do I paste this code in? Using the advanced editor?
Thank you
Jano
@JanoLehocky No, I wasn't paying attention and gave you a DAX solution so you can't do that in Power Query, just as a DAX calculated column.
I just noticed the dates were not formatted right in the second table:
Personnel no. | Personnel Name | Salary Band | Reason on Pay Record | Basic Pay Record Start Date | PROMO STATUS | PROMO CYCLE |
123 | Jean-Luc Picard | 12 | Merit Review | 03/01/2019 | ||
123 | Jean-Luc Picard | 13 | Promotion | 03/01/2020 | Promotion | On Cycle |
123 | Jean-Luc Picard | 13 | Other | 10/19/2020 | ||
123 | Jean-Luc Picard | 13 | Other | 11/06/2020 | ||
234 | Benjamin Sisco | 10 | Merit Review | 03/01/2019 | ||
234 | Benjamin Sisco | 11 | Merit Review | 03/01/2020 | Promotion | On Cycle |
234 | Benjamin Sisco | 11 | Promotion | 06/25/2021 | ||
3434534 | Thomas Riker | 10 | Other | 01/01/2019 | ||
3434534 | Thomas Riker | 10 | Merit Review | 03/01/2019 | ||
3434534 | Thomas Riker | 10 | Other | 01/01/2020 | ||
3434534 | Thomas Riker | 10 | Merit Review | 03/01/2020 | ||
3434534 | Thomas Riker | 10 | Other | 01/01/2021 | ||
666667 | Travis Mayweather | 8 | Other | 02/04/2019 | ||
666667 | Travis Mayweather | 9 | Other | 11/03/2019 | ||
666667 | Travis Mayweather | 10 | Other | 07/01/2020 | Promotion | Off Cycle |
Although I am not quite sure why your Desired Output table is not following the rule
1. Where/when the actual promotions are happening. (a promo is an increase in salary band),
you can give a try to the code below:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndJdC4IwFAbgvzK8Nraz2Ye3XUZSVHfhxbBBK9xgWtG/bzOhZfiVF8IOezznPXg8BkBZEAYrwdVkfcvQVmbcnGwFqH0lwsgS7cRdioc9EoYJYEogDtKwg7ry1uhcl1Irz1HS7zblWRh3IBjisQYwmX0MZZEtLoW68FwqtJdFpqsv9yZrk9Aue3pCcyV2zqljUDEWsWha0cNZ57xAO3mt1+Dls438KbtRd8JxDetwfzUcZH8avtcyc8/cGcPvskAJfz4Er+8ufEcxiT7pOl3c+GPYQPc9qJcufQE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personnel no." = _t, #"Personnel Name" = _t, #"Salary Band" = _t, #"Reason on Pay Record" = _t, #"Basic Pay Record Start Date" = _t]),
Grouped = Table.Group(Source, {"Personnel no."}, {{"Gr", each let t=_ in Table.AddColumn( Table.AddColumn( Table.AddIndexColumn(t, "i",-1,1), "PROMO STATUS", each try if Number.From([#"Salary Band"]) > Number.From(t[#"Salary Band"]{[i]}) then "Promotion" else "" otherwise ""), "PROMO CYCLE", each if [#"PROMO STATUS"]="" then "" else if Date.Month(Date.From([#"Basic Pay Record Start Date"])) =3 then "On Cycle" else "Off Cycle" ), type table }}),
Removed = Table.RemoveColumns(Grouped,{"Personnel no."}),
FINAL = Table.ExpandTableColumn(Removed, "Gr",
List.RemoveItems (Table.ColumnNames(Removed[Gr]{0}), {"i"}))
in
FINAL
Result:
Figured this out with a work colleague! Thank you so much Jakinta!!!
Cheers
Jano