Forum Discussion
New Status Conditional/Calculated Column/ Measure or how would you do it?
Hello everybody, need your help cracking this out.
Working with a movement adherence report, got two tables
Table 1 dataset columns are: Employee ID, Full Name, Last Calification (values can be 1A, 1B, 2, 3A, 3B, 4A, 4B)
Table 2 dataset columns are a lot, the ones with relevance are also Employee ID (create a reationship to Table 1 Employee ID), and column Type of movement.
what I need is to create a new column ¨Status¨ in Table 2, to say if the movement is OK or Not OK according to the following policy.
| ACTION | 1B | 1A | 2 | 3B | 3A | 4B | 4A |
| Promotion band up | N | N | N | N | Y | Y | Y |
| Promotion within band | N | N | Y | Y | Y | Y | N |
| Lateral move (same band) | N | Y | Y | Y | Y | Y | N |
| Involuntary TO | Y | Y | N | N | N | N | N |
| Voluntary TO | Y | Y | N | N | N | N | N |
If Column of Table 2, Type of movement is ¨promotion band up¨ and data in the column of Table 1, Last Calification is: 1B, 1A, 2 or 3B column should retrive Not OK, otherwise if it's 3A, 4B or 4A, it should be OK. The same logic to all types of movements according to the table above.
Which would the best way to have this done?
Thanks & regards!!
In Power Query you can unpivot the Policy table and replace N/Y with Not OK and OK:
let Source = Excel.CurrentWorkbook(){[Name="Policy"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ACTION"}, "Calification", "Status"), #"Y/N to OK/Not OK" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Status", each if _ = "N" then "Not OK" else "OK"}}) in #"Y/N to OK/Not OK"Now you can merge Table 2 with both Table1 and the Policy table:
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Type of movement", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Employee ID"},Table1,{"Employee ID"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Last Calification"}, {"Last Calification"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded NewColumn",{"Type of movement", "Last Calification"},Policy,{"ACTION", "Calification"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Status"}, {"Status"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn1",{"Last Calification"}) in #"Removed Columns"
8 Replies
In Power Query you can unpivot the Policy table and replace N/Y with Not OK and OK:
let Source = Excel.CurrentWorkbook(){[Name="Policy"]}[Content], #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"ACTION"}, "Calification", "Status"), #"Y/N to OK/Not OK" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Status", each if _ = "N" then "Not OK" else "OK"}}) in #"Y/N to OK/Not OK"Now you can merge Table 2 with both Table1 and the Policy table:
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee ID", Int64.Type}, {"Type of movement", type text}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Employee ID"},Table1,{"Employee ID"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Last Calification"}, {"Last Calification"}), #"Merged Queries1" = Table.NestedJoin(#"Expanded NewColumn",{"Type of movement", "Last Calification"},Policy,{"ACTION", "Calification"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries1", "NewColumn", {"Status"}, {"Status"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn1",{"Last Calification"}) in #"Removed Columns"Hi MarcelBeug thanks for your time and your input on this matter, I really appreciate it!
Definitely I was going to give this a try, but due a problem with the dataset, Type of movement information isn't reliable generated in India.
A patch or temporary solution would be to have it also calculated it in the report,, could you please also help me cracking this out:
Type of Movement can be calculated with this other two columns: Previous Band and New Band
If the value of New Band remains exacty the same as Previous Band column, the column Type of movement should be: ¨Lateral Movement¨
If New Band changes only the letter after ¨-¨ from B to A, but the roman numeral stills the same: ¨Promotion within Band¨
If New Band changes the roman numeral before ¨-¨ and the letter also or even if the letter remains the same: ¨Promotion Band Up¨.
Also, if ID Employee in this table matches ID Employee in Termination Table, should return in a new column TO movements the value ¨Involuntary TO/ Voluntary TO¨ that is in the Status column of that table.
After having this, I should have no problems in implementing your prior solution!
Thanks & regards!
