Importing Dataverse table with Microsoft Entra ID 'aaduser' lookup column

[RH10]

Hello everyone,

I'm running into a weird problem when importing a Dataverse table that contains a lookup column to the Microsoft Entra ID 'aaduser' table into Power BI Desktop. All of the values in this column are a string of characters with dashes in between, which I assume is the 'userid' value for a particular user. However, I want to import just the display names instead. How do I go about accomplishing this?

I've tried importing the 'aaduser' table directly after selecting Dataverse in the Get Data option so that I can create a relationship between these tables, but it doesn't show up. I've also tried using the OData connector to import the table, but refreshing is very slow because there are tens of thousands of users, so I would like to avoid that. Does anyone else have any ideas?

Thank you in advance for your help.

[Anonymous · Accepted solution]

Hi @RH10 ,

which I assume is the 'userid' value for a particular user

Yes it is userid.

Please try to connect to the system user Table, it contains the user name and user id. The field corresponding to id is Azure AD Object ID:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ginsardi]

Hoping your account is still active and you can help. Here's my issue:

If I deploy my solution in our default environment, all active users will be present in the systemuser table, however, the systemuser table does not contain all of the same info as the aaduser table, particularly the "Office Location" field which we use to designate our staff to a particular practice, so using the Entra ID User table would be ideal, but since its a virtual table it bricks the embedded Power BI report in the model-driven app and can't be added as a data source in Power BI Desktop. 

So my question for you is, what is the best way to ensure I have all possible user information in a table that will update automatically and can be used in Power BI for reporting? We have around 3,000 active staff members so creating my own table and adding/updating/removing using Power Automate isn't a viable solution, but there has to be a way for me to access a live record of staff in my org to be able to assign them an asset. Any help/guidance you can provide would be much appreciated.

Thank you!

-Greg

[MikaylaRichard]

Did you ever get a response to this question? I have the same inquiry.

[ginsardi]

I did not. What I'm going to end up doing is using the AAD Table and just using a separate custom power bi report. It's a pain though because you can't pull the AAD Table into Power BI directly, so I'm using a dataset that was published for a separate Power BI App (Powerstacks - BI for Intune App) that pulls in all user data and then creating a relationship betwen my Dataverse Table and the User Table in the BI for Intune dataset to link together my user fields and pull them into the report I build. 

Not my favorite way to do it, but it will work 😔

[MikaylaRichard]

I also ended up implementing a workaround. My report also utilzes the "User" table (which looks up data in the AAD table). I ended up adding text columns to the User table for the data I needed. New columns:

1. Company (text)

2. Department (text)

3. Manager Display Name (text)

4. Manager UPN (text)

I then created the following flow to copy the data over from the user's Office 365 profile when there is a change:

Glad you found at least a workaround for what you needed! Thanks for the response.

[RH10]

It worked, thank you very much!