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 IDimaMD you are right. I need to learn more and I'm doing it.
I have no more than 4 Values in total and different combinations of filled-in and blank fields. In most cases, I have 3 values filled in but I don't want to hardcode this logic to the formula if possible. I have very little programming experience but maybe it's possible to read values in a cycle 4 times and add value to the result if the previous value doesn't equal the current. Just an idea...
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
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
- Rinat
thank 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 _ <> "")), " ;"))