Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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?
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.
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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.
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])
Did I answer your question? Mark my post as a solution! Proud to be a Super User!
Connect with me!
Stay up to date on
Read my blogs on
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
NEVERMIND GUYS
The answer is in here
https://community.powerbi.com/t5/Desktop/Combine-columns-if-not-null-or-empty/m-p/187990#M82655
Thank you. Good advice with the ^
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.
Would this not just be the same as duplicating your table, removing everything but the survey column, removing duplicates then adding an index column?
It would indeed! Thanks for that info. I'm newer to the whole relationship concept. I appreciate it!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |