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

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!

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    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.

    • franorio's avatar
      franorio
      Helper III
      9 years ago

      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!