Normalizing Text in a Field

[JohnThomas]

Hi all,

Looking for some help that seems like it should have been solved before, but I can't seem to find it. If someone can provide a link I would appreciate it!

Problem:

I have bunch of csv files I am importing from a folder.  One of the trasfomations I need to make is to normalize the employee's name.  I have a consistant Employee ID, but evertime there is a change in status (pay increase, job title change, department change, etc.) the admin that enters the employee's name always does it differently.  Sometimes there are comas, sometimes there two spaces between first and last name, sometimes the middle name is there, other times just the middle initial, etc.  When the Employee ID and Name are placed on a pivot table, this causes all kinds of havoc!

What I need:

Looking for the best way to pick the most recent transaction and change all the previous names to that name. (Yes, sometimes people get married, so the name change would be a valid one.)

What I have tried:

Using Power Query "Replace Values" to fix the names.  This works, but they have to be done one at a time and every week there's more corrections that have to be made.  The list of "# Fix Name 1" is getting rather long.

What I think might be the best solution:

1. Create another table (tblEmployeeNames) with Employee ID and Name
2. Delete the Name from the source data using Power Query.
3. Set a relation in the data model to the tblEmployeeNames table.

Issue with above is I don't always know when we add employees.  Is there a way to dinamically only append new names to tblEmployeeNames?

Thanks in advance...

[VP · Accepted solution]

There are 2 options that I used in past for similar situation.

Option 1:- Preferred option

• Use Active directory as data source. Pull Employee ID and Given Name from Active directory
• Connect Employee ID from Active directory to Employee ID from CSV file.
• Use Given Name for Power BI interaction.

Option 2:-Table reference (As you suggested)

1. After combining All CSV files, Create new query using first query as a reference.
2. Remove all column except Employee ID
3. Remove Employee ID Duplicate
4. Compare this list to separate table where you maintain employee ID Vs Name reference.
5. Keep Only new entry:- Load this to Power BI report to keep track of new entry. Also this list all record that you need to add it to master table
6. Append filtered list (Only new Entry) to existing records.
7. Use new table for name display.

Open to learn how others have solved in past

[JohnThomas]

Can you expand on Step 4?  How do I compare two tables for what's in one table and not the other (in Power Query)?