Forum Discussion
Helper IHow to concatenate unique values?
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
Helper Ithank you, Anonymous ! It looks like it worked as expected. As I'm new, let me ask you the following: would it be correct to pass the list in the following way?
List.Select({[column1],[column2],[column3]})List.Select({[column1],[column2],[column3]})
I've edited the code manually in the advanced editor. What if I want to add column via wizard?
For some reason the following didn't work for me:
=Text.Combine(List.Distinct(List.Select({[column1],[column2],[column3]}, each _ <> "")), " ;"))
Hi DimaMD,
In fact, the first _ operator in Table.AddColumn function means the current row so I used the Record.ToList(_) to transform the current row values as a list. (power query use 'record' format to store table row)
M Language Operators - PowerQuery M | Microsoft Docs
If you want to exclude or only check specific fields, you can nest a 'Record.SelectFields' function in it to pick up specific fields.
Record.SelectFields - PowerQuery M | Microsoft Docs
Here is the sample and it only works on the 'column1,column2,column3' that I defined in the function: (I try to change the codes styles to more readable format)
#"Added Custom" =
Table.AddColumn(
#"Changed Type",
"Combine",
each
Text.Combine(
List.Distinct(
List.Select(
Record.ToList(
Record.SelectFields(
_,
{
"Column1",
"Column2",
"Column3"
}
)
),
each _ <> ""
)
),
";"
)
)
Regards,
Xiaoxin Sheng