Forum Discussion
Helper IHow to concatenate unique values?
Hello everyone.
Can someone please help me to get the DAX formula to create DAX formula to create a custom column which contains concatenated values from other columns with "; " as the delimiter. I just started my DAX journey and tried to use COMBINEDVALUES but don't know how to make them contain unique values. I'm looking for a DAX substitute of =TEXTJOIN(", ",TRUE, UNIQUE(G3:I3,TRUE)).
Hope it makes sense 🙂
Thank you very much in advance.
Example:
| Value 1 | Value 2 | Value 3 | Result |
| John | John | John | John |
| Marie | Alex | Alex | Marie; Alex |
Hi Rinat,
According to your description, I think the Dax function doest not suitable analyze multiple fields. Perhaps you can try to use Power query formulas to do this operation.
I create a custom column with M query functions to extract and analyze current row field values, concatenate 'nonblank' and distinct values with character ";":
#"Added Custom" = Table.AddColumn(#"Changed Type", "Combine", each Text.Combine(List.Distinct(List.Select(Record.ToList(_), each _ <> "")),";"))Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSQcKxOtFKTkAWDENEnIEsIHIGY5CAC5DlAhF0VYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Combine", each Text.Combine(List.Distinct(List.Select(Record.ToList(_), each _ <> "")),";")) in #"Added Custom"Regards,
Xiaoxin Sheng
20 Replies
- ValtteriN
Community Champion
Hi,
Getting a distinct list of values like this would be really straightforward using powerquery. Might I inquire what is your end goal after the data is in this format as described in your post?
RinatHi. If this would be easier to do in power query, I can certainly do this there, especially as I learned that performance is better while using it. If you can give me an example for 3 values as above and what if I have more than 3. Thank you a lot!
- ValtteriN
Hi,
For using powerquery I would unpivot the columns in question and use remove duplicates on the unpivoted column. This doesn't provide you with a list with separator as you described and that is the reason I asked what is your end goal. Here you example data is names so I guess you want a list of names?
In that case PQ will be sufficient. However, if you insist on a list with separator COMBINEVALUES is a good option.
Some examples of what I mean:
