How to edit USERPRINCIPLENAME()

[FkZ0]

I want to use the USERPRINCIPLENAME() function in a dynamic text message on my dashboard that displays a welcome message to the particular user. However the USERPRINCIPLENAME() function returns the corporate email of the person as opposed to their name, how do I manipulate the USERPRINCIPLENAME() to return a person's actual name?

[christinepayton]

There's a video on how to do this on YouTube. They're using USERNAME(), but the same applies for USERPRINCIPALNAME(): https://www.youtube.com/watch?v=uvmsQU0dz0w 

[foodd]

USERNAME() is not likely to contain First Name, though the video is helpful.

[christinepayton]

Right, you'd need some kind of users dimension table. Unless the userprincipal is firstname.lastname@whatever.com and you can split on the period. If all users log into SharePoint, you could use the UserInformationList in the root SP URL to get a table of user profiles. 

[foodd]

Hopping through SharePoint is overkill, when User Directory Service is going to be the source of truth which is where the information is stored and the authentication venue.

[christinepayton]

How does one do this? I thought the Active Directory connector in Power BI was for local AD, not AAD. 

[foodd]

A traditional approach would be to use Azure Powershell.  Getting a hand

to set up this and whatever scripts you need when starting out is helpful.

General Steps -

Azure PowerShell (Documentation)

-  Module is Az.Resources

-- Connect-AzAccount 

-- Get-AzADUser | ConvertTo-CSV | Out-File C:\ADUsers.csv

Import CSV using Power Query for your User Dimension Table.   

This is a manual process, which can be set up as a Scheduled

Task, Power Automate, and ideally stored as a 

Power BI Dataset so that you do not have to go through

the mechanics of pulling User or Asset Data when you need it.

A gentle introduction by Reza Rad.

[christinepayton]

To be fair, that sounds like a substantial lift if you're not someone with local and Azure admin permission, and a VM... Takes about 2 minutes to get it out of SharePoint as a read-only user, and includes all the profile fields like the bio, skills, about me, etc. The data quality there is decent if you're syncing fields from AD and your staff all use SharePoint... but that's the hitch. 🙂

[foodd]

If you want the person's actual forename/surname, then you would probably need to build a dimension table from your Active Directory records, something like this:

1) Create a new query, and connect to your Active Directory domain.

2) Find the User table and import it.

3) Find the [User] column that has nested records in it, expand, and select the [mail] column to expand.

4) Select [displayName] and [mail] and remove other columns.

You should now be able to use this as a dimension table and relate it to a calculated table that contains USERPRINCIPALNAME to translate it to a 'friendly' name.