Group email adresses by domain

[ARag]

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?

[vivran22]

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]

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 · Accepted solution]

@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