Replacing Values based on a Record?

[Spencer_Bye]

Currently, I am using Power BI to pull data from our project management system so that we can visualize the current state of our work orders. We are tracking data such as who the work order is assigned to, which customer requested the work order, and other bits of data like that. However, the SQL Server which we are pulling this data from stores this information using the employee's ID number. This is very inconvenient and I would rather be able to work with the employee names. However, we are unable to make changes to the SQL server directly.

I have a report saved in power query that matches ID Numbers with Names. I could simply go through a do a large quantity of replace values but given there are 400+ employees I was wondering if there was a way to do some form of automatic replace where the system checks the number against the report and replaces any occurance of that value in a particular column with the name that corresponds with that number.

[jennratten · Accepted solution]

Hi @Spencer_Bye - Power BI can return the values you are needing using the basic functionality.  You will need to add the SQL table with your project management data to your data model and also load the SQL table which contains the employee name and ID.  Then in Power BI on the data model tab, draw a relationship between the two tables on the employee ID field in both tables.  When you load the two tables to the model PowerBI may create this relationship automatically for you.  Then add a table visual on a report page/tab, select the employee names from the employees table and the project information from the projects table.  That should result in one table with your projects and employee names that you are looking for.

If you are needing to join these tables in Power Query in order to perform additional transformations on the joined data, then you can merge the two tables.  Select the table/query for your projects data.  On the ribbon, go to Home and choose Merge Tables >  Merge Queries > Merge Queries.  Click the employee ID field in the projects table.  Then choose the employees table as the 2nd table and select the employee ID field.  Choose left outer join to return all rows in the current projects table and matching values from the employees table.  When the query is done processing, scroll all the way to the right to see the new column that has been added.  Click the diverging arrows button in the column header and expand the column to add the employee names to the table.