Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ARag
Frequent Visitor

Group email adresses by domain

Hi,

 

I have an exchange inbox imported to my PowerBI report and I want to group the different email domains to reflect the same category. 

 

For example

Recieved emails: 

XXXX@123.com

- 3 emails


YXYX@123.com

- 4 emails

 

XYXY@456.com 

- 2 emails 

 

Desired result: 

@Anonymous.com

- 7 emails

 

@456.com

- 2 emails

 

Is there any way to do this?

 

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

@ARag 

 

However, Does this require more manual input as new email addresses are added?  - No, you need not to. You have to define the Column from Example once, and if you are satisfied with the output then any new record will be updated accordingly.

Is there anyway to automate it? I'm thinking something like a statement that trims down and removes characters before the @ character.  

Alternatively, you could do this in two steps:

  • Add Column > Extract > Text After Delimiter (Use @ as delimiter) - This will separate the domain in an additional column
  • Transform > Format > Add Prefix (Use @ )  - This will format the column as @abc.com

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

View solution in original post

3 REPLIES 3
vivran22
Community Champion
Community Champion

Hello @ARag ,

 

You may use following steps in Power Query:

  • Extract the domain name with the help of Add Columns > Column from Example
  • Then use Group By feature to get the count

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

ARag
Frequent Visitor

Hi @vivran22 

 

Seems to work! However, Does this require more manual input as new email addresses are added? 

Is there anyway to automate it? I'm thinking something like a statement that trims down and removes characters before the @ character.  

vivran22
Community Champion
Community Champion

@ARag 

 

However, Does this require more manual input as new email addresses are added?  - No, you need not to. You have to define the Column from Example once, and if you are satisfied with the output then any new record will be updated accordingly.

Is there anyway to automate it? I'm thinking something like a statement that trims down and removes characters before the @ character.  

Alternatively, you could do this in two steps:

  • Add Column > Extract > Text After Delimiter (Use @ as delimiter) - This will separate the domain in an additional column
  • Transform > Format > Add Prefix (Use @ )  - This will format the column as @abc.com

 

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors