Forum Discussion
New Status Conditional/Calculated Column/ Measure or how would you do it?
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"
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!
Steps to take:
1. Duplicate the columns for Previous Band and New Band,
2. Split each of those copies at delimiter "-",
3. Merge the table with the Termination tableand you have all data on each row to
4. Add a conditional column with the Movement Status.
Mind PQ's case-sensitivity though: e.g. "Promotion band up" is not the same as "Promotion Band Up"
