Normalizing Text in a Field

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