Is it possible to create a unique identifier based on the contents of two columns?

[Anonymous]

I need to create a unique identifier column for a list of surveys submitted, and I want the surveys submitted for the same category to be given the same ID. Is this possible in Power BI? 

Here's an example:

Name               Date               Survey

Ben Williams    (6/12)              Cashier

Kelly Marks      (6/12)              Cashier

Max Ferrar       (6/12)                Store

I'd like to assign IDs to this list like so:

ID               Name               Date               Survey

1            Ben Williams         (6/12)              Cashier

1             Kelly Marks          (6/12)               Cashier

2             Max Ferrar           (6/12)                Store

Any suggestions?

[SteveCampbell]

Expanding, here is one way to do in Power Query:

1. Open advanced editor

2. Look at the last step, where it says 

in
 #"SOME STEP"

3. For whatever value you have for #"SOME STEP" , copy that and replce in the code below:

     ,G = Table.AddIndexColumn(Table.Group(#"SOME STEP", {"Survey"},{{"", each null}}), "ID", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"SOME STEP",{"Survey"},G,{"Survey"},"G",JoinKind.LeftOuter),
    #"Expanded G" = Table.ExpandTableColumn(#"Merged Queries", "G", {"ID"}, {"ID"})

in
    #"Expanded G"

4. Now, copy this whol code and paste over

in
 #"SOME STEP"

The bits I highlighted in Green is the column name, so this can be replaced if you want to use a different column.

[Anonymous]

I'm struggling with this. It seems totally logical but i don't understand what i'm meant to add into "Some Step".

I'm trying to make a unique identifyer based on the combination of 3 columns. So similar to this need.

I'll happily make another post but was hoping for more info around how this would look, specifically against the example.

[SteveCampbell]

This is maybe if you've had some experience editing M code.

If you want to make a unique identifier on 3 existing columns, in Power Query Editor, you could click Add Collumn > Custom Column. 

For the formula, you can add all the column names seperated by an ampersand (&) to concatenate. So, something like 

[Column1] & [Column2] & [Column3] 

Be aware that they have to be the same format (text, number etc).

I would also reccomend using a character to concatenate in the middle, I usually use a carrat (^).  This can stop repeats, especially for numbers - for example, if you join 51 & 11 = 5111, and 5 & 111 = 5111.

So the code could be:

[Column1] & "^" &  [Column2] & "^" & [Column3] 

As you are concatenating text, all columns must be text.

You can either change them all the columns to text and combine, or if you have numbers, wrap in

Number.ToText([Column1])

[augustodarruda]

Hey, thanks a lot, you helped me a lot, but I am struggling with something here, and perhaps you may help me.

I am using 6 columns to make one that is unique. But what happens is that some of these fields have 'null' values, and when I conatenate the text, the result ends up bein 'null', and I don't want that.

Is there a way to ignore if the cell is null and just add the text?

Thanks in advance

[augustodarruda]

NEVERMIND GUYS

The answer is in here
https://community.powerbi.com/t5/Desktop/Combine-columns-if-not-null-or-empty/m-p/187990#M82655

[Anonymous]

Thank you. Good advice with the ^

[Anonymous]

I'm struggling with this. It seems totally logical but i don't understand what i'm meant to add into "Some Step".

I'm trying to make a unique identifyer based on the combination of 3 columns. So similar to this need.

I'll happily make another post but was hoping for more info around how this would look, specifically against the example.

[jthomson · Accepted solution]

Would this not just be the same as duplicating your table, removing everything but the survey column, removing duplicates then adding an index column?

[Anonymous]

It would indeed! Thanks for that info. I'm newer to the whole relationship concept. I appreciate it!