cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbiuser2 Member
Member

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

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

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

7 REPLIES 7
Super User
Super User

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

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

pbiuser2 Member
Member

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

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

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

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.

 

Garol Regular Visitor
Regular Visitor

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

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.

 

 

Garol Regular Visitor
Regular Visitor

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

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.

 

 

Highlighted

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

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])

 

Garol Regular Visitor
Regular Visitor

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

Thank you. Good advice with the ^