Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ...
Need Help
  • jthomson's avatar
    jthomson
    8 years ago

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

SteveCampbell's avatar
SteveCampbell
Icon for Memorable Member rankMemorable Member
7 years ago

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's avatar
augustodarruda
Frequent Visitor
5 years ago

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