Latest Enterprise DNA Initiatives

Matching a partial Employee code


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.

Strata Staff Utilization.pbix (3.2 MB)

Hi @Krays23,

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.

    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])


with this result.

1 Like


Try this

Strata Staff Utilization.pbix (3.2 MB)

1 Like

Thanks Melissa that works fine how can i incorporate it into the Headcount table?

Source = #“Head Count (Hassan)”,
#“Removed Columns” = Table.RemoveColumns(Source,{“Report Date”}),
#“Renamed Columns” = Table.RenameColumns(#“Removed Columns”,{{“ID Number”, “Employee Number”}})
#“Renamed Columns”

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.