How to concatenate unique values?

Hi, Rinat  Yes, the result will be as follows

HI Rinat try it

Result = 
IF(
     [Value1] = [Value2],[Value3],
            IF(
            [Value1] = BLANK() && [Value2] <> [Value3], COMBINEVALUES(";", [Value2],[Value3]),
                IF([Value2] = BLANK() && [Value1] <> [Value3], COMBINEVALUES(";", [Value1],[Value3]),
                        IF([Value3] = BLANK() && [Value1] <> [Value2], COMBINEVALUES(";", [Value1],[Value2]),
                            IF([Value1]<> BLANK() && [Value2]<> BLANK()&& [Value3] <> BLANK()&& [Value1]<>[Value2], COMBINEVALUES(";", [Value1],[Value2]),
            [Value3])
            ))))

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

DimaMD unfortunately, after some testing, I cannot confirm the formula worked for me. In your screen above I expected to see Tj,Ta. However, I see only Ta in the last row? Can you please explain how your formula works? Thank you very much for your help.

hi Rinat 
Using the IF function, I compare values ​​1 and values ​​2 and assign values3, otherwise I order COMBINE VALUES (";", [Value1], Value2])
example
if John = John , John
if Marie = Alex, COMBINEVALUES(";", [Marie], [Alex])
result 
John
Marie;Alex
I hope you understand

I advise you to familiarize yourself with this feature
https://docs.microsoft.com/en-us/dax/if-function-dax
https://dax.guide/if/
Your task is complicated if you need to compare other Values, how many Values ​​do you have?

DimaMD 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... 

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 _ <> "")), " ;"))

Hi DimaMD. I'll keep your solution in a back pocket and will test it when I can. Thank you for your help!