Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to Solution.
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 table
and 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"
Thanks for everything @MarcelBeug! You're awesome!!!
Did your steps as described and my Type of movement issue is solved!
Also merged with the Policy Table and also worked! I'm having just an issue with the Status of some 1A values, i'm trying to figure out what is happening
Thanks again for all of your help!!! Regards!
I guess the "1A" is not (correct) in the Policy table; it might not exist or as "1a" (lower case) or e.g. with a trailing space.
I suspect that the cases where "1A" gives "No OK", are based on the Termination table.
Hello @MarcelBeug!!
After a few weeks out of work, I'am returning to the parameterization of this report, I wanted to know if you can help me with the following:
https://community.powerbi.com/t5/Desktop/Calculated-Formula-to-count/td-p/152944
Thanks & Regards!!
You are right again, the 1A had a trailing space in the table1, the one with all last calification that was merged in table2.
Thanks for all your help!
Regards!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |