Create new column from distinct values of other two columns in the same table

[KoalaPBI]

Hi Community,

How can I create a new column (NOT Table) from distinct values of other two columns in the same table?

like:

where DAX for new columnis smething like:   union(distinct(values(col1)),distinct(values(col2))

Thanks!

[v-jianboli-msft · Accepted solution]

Hi @KoalaPBI ,

Please try:

First create an index column:

Then create a new column:

Column =
VAR _a =
    DISTINCT (
        UNION (
            SELECTCOLUMNS ( FILTER ( 'Table', [Col1] <> BLANK () ), "Col1", [Col1] ),
            SELECTCOLUMNS ( FILTER ( 'Table', [Col2] <> BLANK () ), "Col2", [Col2] )
        )
    )
VAR _b =
    ADDCOLUMNS ( _a, "Rank", RANKX ( _a, [Col1],, ASC ) )
RETURN
    MAXX ( FILTER ( _b, [Rank] = [Index] ), [Col1] )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@KoalaPBI , A new table in Power query

List.Zip({Table[Column1], Table[Column2], List.Distinct(List.Combine({Table[Column1], Table[Column2]}))})

Power BI Combine Tables Vertically(Row by Row). Combine Queries without key: https://www.youtube.com/watch?v=v-dGYyKk79w

Power Query- List.Combine: https://youtu.be/yySRoH51Sb0

Then use convert to table option from Transform

[KoalaPBI]

@amitchandak thanks, but I want to create the new column (from distinct values of col1 & col2)  in the same table not a new table.

[amitchandak]

@KoalaPBI , Doing the same thing but replaced current table in the same code in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYnWglJyArTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    _new =List.Zip({#"Changed Type"[Column1], #"Changed Type"[Column2], List.Distinct(List.Combine({#"Changed Type"[Column1], #"Changed Type"[Column2]})) }),
    #"Converted to Table" = Table.FromList(_new, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}})
in
    #"Changed Type1"