I have a challenge here where by my SAP system logs an employee clocking a part at work, however it doesn’t capture the complete ID number of the employee
Our full time employee numbers range from 10000 to 101999
Our Contract employee numbers range from 8000000 to 8000991
When i download the confirmations from SAP i see the employee ID column however its a mixture of numbers and not the complete ID
For example for full time employees ranged 10000 to 10999 it will only capture the last 3 digits example 001 and not the full id of 10001
For full time employees ranged 101000 and up it captures the last 4 digits example 1001 and not 101001
For contractors it captures the full id so this isnt a issue.
My question is in Power BI query editor create a filter table that will recognize the SAP employee number against the employee automatically?
In the attached PBIX file there is a table called Head Count which contains all the official employee numbers from HR and then there’s a table called COOIS Employee Append this is a unique list of ID numbers captured from SAP employees physically clocking parts and as you can see its an incomplete portion of the ID in the head count.
What i want to do is in the Head count table link these incomplete IDs to the correct ids if possible so i can filter against the complete IDS.
You might have to update the code a bit but see if this let’s you create an Employee Key, just copy the full code below into a new Blank Query.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZJLesQwCIPvMusueBgwZ5lv7n+NpkW2ZhP9joUwTt7vV+9+fX7+dENltHw0A7qgCoVvYe3I8Xm/u0b31O1qaI4m9vOs4UPuVuxLQA0q0q1fLGTMMry/2MlBT0WRV15eOpnVAZ360slMzJzS0KmNbqhDFTp5UTNDeEHPGj5DvWFf5k5WT98V4182/Rb6Ovp6HR3BlTo+mcfE+Jrj+fE5fGhnOLbh09mUoanFDGO4MMOhzSbVdNZak6K5oZOmGE6f6z9gB1AieTz4Ox5AtawDfrb8VOHXU8GRHjg5dsx2ytXwh/xjEWmoL3SiXMwm0pt2MZgbX2+ZoEkMIr2qF4Vhwm7Ck8n1ahfxdlOOqTuJLIt7JbqayDKn1+g1GpRlSoPc2aSbqMQ55PP8/AI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Personnel number" = _t]),
InsertEmpCode = Table.AddColumn(Source, "Full Employee code", each if Text.Length([Personnel number])=2 then "100" & [Personnel number] else if Text.Length([Personnel number])=3 then "101" & [Personnel number] else if Text.Length([Personnel number])=4 then "10" & [Personnel number] else [Personnel number])
in
InsertEmpCode
From the Head Count table, select “Merge Queries” (make sure the data types are the same for those key fields). After you’ve pressed OK, with those side-ward arrows you can expand the data.
