Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
franorio
Helper III
Helper III

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.

 

ACTION1B1A23B3A4B4A
Promotion band upNNNNYYY
Promotion within bandNNYYYYN
Lateral move (same band)NYYYYYN
Involuntary TOYYNNNNN
Voluntary TOYYNNNNN

 

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!!

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

View solution in original post

8 REPLIES 8
MarcelBeug
Community Champion
Community Champion

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"
Specializing in Power Query Formula Language (M)

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!

calculo bandas.PNG

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"

 

Specializing in Power Query Formula Language (M)

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

 

1a band error.PNG

1a dos.PNG

 

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.

Specializing in Power Query Formula Language (M)

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.