Forum Discussion

Kavya123's avatar
Kavya123
Helper III
3 years ago
Solved

Remove Duplicate values without deleting a row

Hi All,

 

I need to remove the duplicate values in a row without deleting the row. For example, in third row output should be "A" only instead of "A,A". 

I am trying with this expression 

Measure = CALCULATE(CONCATENATEX( VALUES('Organization Detail'[Industry]),'Organization Detail'[Industry],","))

 

Industry

A,B

A

A,A

A,P,P

A,P,F

 

 

OutPut

A,B

A

A

A,P

A,P,F

 

Thanks in advance

Need Help
Show and Tell
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    3 years ago

    Does it have to be in DAX or would Power Query work too?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRxUorVAdIQUgdGB+gEwFluSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Industry = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,each [Industry],each Text.Combine(List.Distinct(Text.Split([Industry],",")),","),Replacer.ReplaceValue,{"Industry"})
in
    #"Replaced Value"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago

    Does it have to be in DAX or would Power Query work too?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctRxUorVAdIQUgdGB+gEwFluSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Industry = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,each [Industry],each Text.Combine(List.Distinct(Text.Split([Industry],",")),","),Replacer.ReplaceValue,{"Industry"})
in
    #"Replaced Value"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".