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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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)),",")


Follow on LinkedIn
@ 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"))


Follow on LinkedIn
@ 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)),",")


Follow on LinkedIn
@ 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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors