UserPrincipalName.... is there a way to split this value up?

Power Query
1

In PowerBi there is a Function called “USERPRINCIPALNAME”.

When put in a measure it gives you back in my case… “Chad.Sharpe@td.com”. It appears this function cannot be used in a calculated column.

Is there a way I can split this function so that I can

  1. Remove everything from @ onward
  2. Replace . with a space

The end result would simple be Chad Sharpe.
Without being able to transform it in PowerQ, I’m not sure who I would do it?

1 Like
2

You’re correct that USERPRINCIPALNAME() is typically used in measures, not in calculated columns. But if you want to manipulate the string it returns in a measure to achieve your desired format, you can use other DAX string functions.

Here’s how you can do it:

  1. Remove everything from @ onward: The LEFT() function combined with the SEARCH() function can help you achieve this. SEARCH() finds the position of one text string within another, and LEFT() returns a specific number of characters from the start of a string.
  2. Replace . with a space: The REPLACE() function can help you achieve this.

Combining these, you can create a measure like:

Transformed UPN = 
VAR UPNWithoutDomain = LEFT(USERPRINCIPALNAME(), SEARCH("@", USERPRINCIPALNAME()) - 1)
VAR Result = SUBSTITUTE(UPNWithoutDomain, ".", " ")
RETURN Result

In the measure above:

  1. The SEARCH() function finds the position of the “@” symbol in the USERPRINCIPALNAME() result.
  2. The LEFT() function then extracts all characters up to but not including the “@” symbol.
  3. The SUBSTITUTE() function replaces the dot with a space.

If you used this with the example “Chad.Sharpe@td.com”, the result would be “Chad Sharpe”.

3 Likes
3

That’s Great!!!

You answered my question while I was still on the site looking around… lol

1 Like
4

Always try to give you top service @chad.sharpe lol

1 Like