Duplicate Values in Power BI

When duplicate user accounts exist inside of LMS365, Power BI may throw this error mentioning the duplicate value and the login name associated with them:

 

mceclip0.png

 

This error is most likely caused by the user being deleted from Active Directory and then having the same account recreated again. If this error keeps occurring, we recommend working with your Active Directory team and having them disable users for a specific time period before deleting the account.

 

To allow Power BI to run the report, you can either merge the two accounts together or change the Power BI table relationships. 

 

The benefit of merging users via the API is that their enrollment history is properly transferred to the correct LMS365 account. The downside to this process is that it takes a few more steps and may need to be done multiple times (if the Active Directory processes keep removing and re-adding users). 

Going the Power BI route means that you only have to do this process once. However, if there is a situation where the duplicate accounts are truly two different people, then the Power BI report would refer the manager/subordinate relationship to both of these two different people. 

Both of these processes are described below.

 

Merging Users

In order to merge the users together, we need to utilize the API key to find the User's IDs and to run the merge command. 

  1. Go to api.365.systems.
  2. Authorize your browser by clicking on the "Authorize" button and then entering "api" as the username and the API Key as the password:

    mceclip16.pngmceclip18.png

  3. Once authorized, click on the "Close" Button or the "X" in the Top Right.

    mceclip19.png

  4. Scroll down to the Users section to find the GET /odata/v2/Users command.

    mceclip1.png

  5. Expand the tab and click on the "Try it Out" button.

    mceclip2.png
  6. Click on the "Execute" option down at the bottom.

    mceclip5.png

  7. After the running the command, you are able to scroll down and download the output.

    mceclip6.png

  8. After downloading the file, you can open it with notepad and search the users email using the Control + F keys.

  9. Below is an example of duplicate accounts. They both have the same email, but have different Object Ids. Notice that the account that is at the top has an "IsDeleted" status of True.

    mceclip7.png

  10. After grabbing all of the information for both accounts, head back to the API page and find the POST /odata/v2/Users('{LoginName}')/Merge command. We will enter this command and click on the "Try It Out" button.

    mceclip8.png

  11. For this command, we need to fill out the top option with the Directory Object ID of the Account that you would like to keep. The bottom option will be filled out with the Directory Object ID of the Account to be removed.

    mceclip1.png
    • To determine which account to keep and remove, we recommend to look at the "IsDeleted" field. In almost every case, the account that is removed has the "IsDeleted" field as True.

  12. Once filled out, click on the "Execute" button to run the command. The two accounts will then be merged. To verify that this merge was successful, return to steps 3 through 5 to rerun the command and then download all of the users and verify that the duplicate account has been removed (we recommend waiting a few minutes for verification to allow the changes to go through).

  13. After all of these steps have been run, you should now be able to refresh your PowerBI report with no errors!

Changing Table Relationships in Power BI

  1. To change the Table Relationships in Power BI so that this error never occurs, go to the Data view on the left hand side of the screen.

    mceclip10.png

  2. Click on "Manage relationships" at the top menu.

    mceclip11.png

  3. In the pop-up window, select the Users to Managers relationship and then click Edit: 

    mceclip13.png

  4. Change the Cardinality to "Many to many" and click OK. 

    mceclip15.png


You should now be able to refresh the report and have it run successfully without that error. For more information on Many-to-Many relationships, please visit: Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Docs.

 

 

Was this article helpful?
9 out of 9 found this helpful

Comments

Article is closed for comments.