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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Junaid11
Helper V
Helper V

Concatenate along with duplication removal

Hello,

I am using two custom columns for one query which I feel can be done in one query. So I have the data and I grouped all the data based on ID. The new column was grouped and names as column1. I wanted to concatenate the data for each column if ID was same. I was using below code for the custom column :

Table.Column([column1],"Vendor Description")

 

Then I extracted the value basaed on comma delimeter

 

but the problem was if two rows had same id and the concatenation was done based on above column so if there was same values they were showing like below:

ssssssas.PNG

I wanted it to show only single value if both values were same and if both values were different values then show the different values so for that I created another custom column below:

Text.Combine( List.Distinct( List.Transform([column1][Vendor Description], Text.Trim)),",")

It was working completely fine. Its resuls look like below:

aasddds.PNG

I want to both the codes below to be merge into single in some way I can get concatenation as well removal of duplicate after the comma.

Table.Column([column1],"Vendor Description")

Text.Combine( List.Distinct( List.Transform([column1][Vendor Description], Text.Trim)),",")

 

Help would be appreciated.

Thank you

 

1 ACCEPTED SOLUTION

@Junaid11 Oh, right, Table.Column returns a list. How about:

 

Text.Combine( List.Distinct( List.Transform(Table.Column([column1],"Vendor Description"), Text.Trim)),",")


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@Junaid11 Why not:

Table.Distinct(Table.Column([column1],"Vendor Description"))


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hello @Greg_Deckler ,

It is giving below error:

sdsdsdsdsdsdsd.PNG

@Junaid11 Oh, right, Table.Column returns a list. How about:

 

Text.Combine( List.Distinct( List.Transform(Table.Column([column1],"Vendor Description"), Text.Trim)),",")


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors