Forum Discussion
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?
Memorable MemberExpanding, 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.
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.
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])
Thank you. Good advice with the ^