topic Re: Group email adresses by domain in DAX Commands and Tips

Group email adresses by domain

ARag — Thu, 23 Apr 2020 09:06:37 GMT

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?

Re: Group email adresses by domain

vivran22 — Thu, 23 Apr 2020 09:21:02 GMT

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

Re: Group email adresses by domain

ARag — Thu, 23 Apr 2020 09:29:54 GMT

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.

Re: Group email adresses by domain

vivran22 — Thu, 23 Apr 2020 09:36:16 GMT

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