How to create a unique distinct list from a column with concatenated values?

[sunik]

list	unique distinct values
aa,bb,cc	aa
aa,bb,cc,dd	bb
aa,ff,gg	cc
	dd
	ff
	gg

[ANKITBISANI · Accepted solution]

Hello @sunik,

We have Split columns with delimiters option in the edit queries of the Power BI:

aa,bb,cc	aa	bb	cc
bb ,cc	bb	cc

Once you split , either you can make 3 datasets with different columns using choose "column options"  and then append the three datasets (Remember the name of the column which  you would  append would remain the same .

Dataset1		dataset 2		dataset 3
aa		bb		cc
bb		cc

After appending data set , you shall get 

aa

bb

bb

cc

cc

Then you can use remove duplicates in the "Remove Column" dropdown.

This would work   🙂

Regards

[v-ljerr-msft]

Hi @sunik,

Have you tried the solutions provided above? Do they work in your scenario? If you still have any question, feel free to post it here.

Regards

[ANKITBISANI · Accepted solution]

Hello @sunik,

We have Split columns with delimiters option in the edit queries of the Power BI:

aa,bb,cc	aa	bb	cc
bb ,cc	bb	cc

Once you split , either you can make 3 datasets with different columns using choose "column options"  and then append the three datasets (Remember the name of the column which  you would  append would remain the same .

Dataset1		dataset 2		dataset 3
aa		bb		cc
bb		cc

After appending data set , you shall get 

aa

bb

bb

cc

cc

Then you can use remove duplicates in the "Remove Column" dropdown.

This would work   🙂

Regards

[MarcelBeug · Accepted solution]

Alternatively, you can use Text.Split.

I created the step #"Trimed Text" in the code below by first chosing "Transform - Format - Trim"  and then adjusted the code from Text.Trim to Text.Split.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"List", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{},each Text.Split(_,",")),
    #"Expanded List" = Table.ExpandListColumn(#"Trimmed Text", "List"),
    #"Removed Duplicates" = Table.Distinct(#"Expanded List")
in
    #"Removed Duplicates"