Forum Discussion

franorio's avatar
franorio
Helper III
9 years ago
Solved

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 ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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"
MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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"
franorio's avatar
franorio
Helper III
9 years ago

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!

  • franorio's avatar
    franorio
    Helper III
    9 years ago

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      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"

       

      • franorio's avatar
        franorio
        Helper III
        9 years ago

        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!