Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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.
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.
@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
@amitchandak thanks, but I want to create the new column (from distinct values of col1 & col2) in the same table not a new table.
@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"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |