Output of Unique Name from Set of Data

[Anonymous]

Hello, 

I would like to create a column which has only unquire "Names" from "Name Column" within a set of "Number Columns".

For example, with this input: 

Number	Name
1	Bob
1	Jeff
1	Bob
1	Ryan
2	Ace
2	Bob
2	Ryan

I would like to see this output: 

Number	Name	Unquie Name in Set
1	Bob	Bob
1	Jeff	Jeff
1	Bob
1	Ryan	Ryan
2	Ace	Ace
2	Bob	Bob
2	Ryan	Ryan

Please note that I want a duplicate "Name" output to be reported if with in a new "Number" set

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

First you need to add the index column in Power Query.

Then try the following formula:

Unquie Name in Set = 
VAR _Min = 
    CALCULATE(
        MIN('Table'[Index]),
        FILTER(
            'Table',
            'Table'[Number] = EARLIER('Table'[Number])
                && 'Table'[Name] = EARLIER('Table'[Name])
        )
    )
RETURN IF('Table'[Index] = _Min, 'Table'[Name])

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Hi @v-kkf-msft ,

Thank you so much for your reply and your assistance. 

To stream line this processs, I used the file you attached. 

I treid to enter the data into a "Custom Column" forumla (maybe that is not the right place to put the code?)

and was met with many "Token Eof expected" errors. The first beging at the first line. 

Please help me sort out what I am doing wrong. 

Thank you in advance! 

[v-kkf-msft]

Hi @Anonymous ,

"Unquie Name in Set" is a calculated column. So after you create the Index column, you need to close the Query Editor window and apply changes. Then select this table, click New Column, and enter the formula.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

@v-kkf-msft:

I absolutely cannot thank you enough. Thank you so so so very much! 

Thank you not only for your solution but being patient with me and explaining where I should be inputting the formula.

Thank you!