Custom column that shows rows with the same value in one column

[mattih]

Hi!

I have a table like this:

Web Page	Article
Facebook	Article about cats
Instagram	Article about dogs
Google	Article about birds
Instagram	Article about cats

I would need to create a custom column that would search if the article name is exactly the same between two or more rows it would produce a custom values that tell the web pages that have the same article:

Web page	Article	Custom
Facebook	Article about cats	Facebook & Instagram
Instagram	Article about dogs	Instagram
Google	Article about birds	Google
Instagram	Article about cats	Facebook & Instagram

There are millions of articles in my dataset so I cant use the name of the article in the calculation. Any ideas on solving this? Thank you!

[v-yangliu-msft · Accepted solution]

Hi  @mattih ,

Here are the steps you can follow：

In Power Query:

1. Group By – [Article] – Operation – All Rows

2. Add Column -- Custom Column -- Enter code:

[Count][Web Page]

3. [Custom] – Extract Values

3. Remove [Article]

4. Select [Count] -- Expand

5. Result:

Use Dax:

1. Create calculated column.

Custom =
CONCATENATEX(
    FILTER(ALL('Table'),
    'Table'[Article]=EARLIER('Table'[Article])),[Web Page],"&")

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly