Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sunik
New Member

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

listunique distinct values
aa,bb,ccaa
aa,bb,cc,ddbb
aa,ff,ggcc
 dd
 ff
 gg
2 ACCEPTED SOLUTIONS
ANKITBISANI
Advocate I
Advocate I

Hello @sunik,

 

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

 

aa,bb,ccaabbcc
bb ,ccbbcc 

 

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

View solution in original post

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"
Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
v-ljerr-msft
Employee
Employee

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.Smiley Happy

 

Regards

ANKITBISANI
Advocate I
Advocate I

Hello @sunik,

 

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

 

aa,bb,ccaabbcc
bb ,ccbbcc 

 

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

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"
Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.