Reply

Count e-mail addresses

I have a table that contains different SharePoint sites.
I have a column called Group.Owners where I have the e-mail address to the owner/owners of that site.
There can be none, one or multiple e-mail addresses in that column. And there are of course different values on each row.
The e-mail addresses all have @ and if there are more than one, they are separeted by a semicolon. 
Like this: person.1@domainone.com; person.2@domaintwo.com; person.33@dom.com

I want to, in the easiest way, count the number of e-mailaddresses on each row.
I thought the easiest way was to count how many times @ appears in that column on each row. But I cant figure out how to do this.

I need help! 

1 ACCEPTED SOLUTION

Because some cell is null in Group.Owners column, you can use following code:

List.Count(Text.PositionOf([Group.Owners] ?? "", "@", Occurrence.All))

 

View solution in original post

6 REPLIES 6
ZhangKun
Super User
Super User

Add a custom column,use following code:

List.Count(Text.PositionOf([Group.Owners], "@", Occurrence.All))

 

 

Oh, great solution, but I get this error:

Expression.Error: We cannot convert the value null to type Text.
Details:
    Value=
    Type=[Type]

Because some cell is null in Group.Owners column, you can use following code:

List.Count(Text.PositionOf([Group.Owners] ?? "", "@", Occurrence.All))

 

I can see the ?? "" replaces the null values with empty text. Is there any documentation available about what the ?? exactly does and if there are other options as well? I cannot find anything and was not aware of this functionality

you search "Coalesce" in this pageOperator behavior 

Works well no. Just needed to click "Don't summerize" when I added the values from that column in my table.

Thank you for the code and the help.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)