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
P_P2595
Helper I
Helper I

How to concat text values from the one column with unique id but the text values are repeated values

Input

Id

Description

1

Episode start

1

Episode End

1

Episode start

1

Episode End

2

Episode start

2

Episode End

3

Episode End

3

Episode End

 

Expected Output

Id

Description

1

Episode start-Episode End-Episode start-Episode End

1

Episode start-Episode End-Episode start-Episode End

1

Episode start-Episode End-Episode start-Episode End

1

Episode start-Episode End-Episode start-Episode End

2

Episode start-Episode End

2

Episode start-Episode End

3

Episode End-Episode End

3

Episode End-Episode End

So my question is I've repeated the text value in the description column and I want all the data without removing any duplicate text values.

 

I've tried the below formula

Completed = CONCATENATEX(FILTER(SUMMARIZE(TABLE,TABLE[ID],TABLE[Description]),[ID]=EARLIEST(TABLE[ID])),[Description],"-")

 

Result of the formula

Id

Description

1

Episode start-Episode End

1

Episode start-Episode End

1

Episode start-Episode End

1

Episode start-Episode End

2

Episode start-Episode End

2

Episode start-Episode End

3

Episode End

3

Episode End

 

Not getting duplicate column values with applied formula

Thank you in advance.

2 ACCEPTED SOLUTIONS
Ritaf1983
Super User
Super User

Hi @P_P2595 
You can achieve it with a power query.
If you need all repeats of your id, first step is to duplicate your table:

Ritaf1983_0-1684726008803.png

Then on your duplicate table, you make a concatenation.
I did it in 2 steps

1. Group by:

Ritaf1983_1-1684726175206.png

2. "Corrected" an error by function text. combine:

Ritaf1983_2-1684726445447.png

The last step is to merge this table with the original:

Ritaf1983_3-1684726592233.png 

Ritaf1983_5-1684726862608.png

 

Now just enable the second table from load to a model and press "close and apply"

Ritaf1983_4-1684726712592.png

Result :

Ritaf1983_6-1684726934276.png

Link To sample file 

If my answer was helpful please give me a Kudos and accept as a Solution.

View solution in original post

Ahmedx
Super User
Super User

6 REPLIES 6
Ahmedx
Super User
Super User

pls try this

Screen Capture #1144.pngScreen Capture #1146.pngScreen Capture #1145.png

Ritaf1983
Super User
Super User

Hi @P_P2595 
You can achieve it with a power query.
If you need all repeats of your id, first step is to duplicate your table:

Ritaf1983_0-1684726008803.png

Then on your duplicate table, you make a concatenation.
I did it in 2 steps

1. Group by:

Ritaf1983_1-1684726175206.png

2. "Corrected" an error by function text. combine:

Ritaf1983_2-1684726445447.png

The last step is to merge this table with the original:

Ritaf1983_3-1684726592233.png 

Ritaf1983_5-1684726862608.png

 

Now just enable the second table from load to a model and press "close and apply"

Ritaf1983_4-1684726712592.png

Result :

Ritaf1983_6-1684726934276.png

Link To sample file 

If my answer was helpful please give me a Kudos and accept as a Solution.

Thank you so much for your time and for giving the expected solution.

Now, want to edit a bit more in the question of how to exclude the record if the description pair does not exist.

Input  Output 
IdDescription IdDescription
1Episode Start 1Episode Start
1Episode End 1Episode End
1Episode Start 2Episode Start
2Episode Start 2Episode End
2Episode End 2Episode Start
2Episode Start 2Episode End
2Episode End 3Episode Start
3Episode Start 3Episode End
3Episode End 4Episode Start
3Episode End 4Episode End
4Episode Start   
4Episode End   
4Episode Start   
4Episode Start   
5Episode Start   
5Episode Start   

 

In short I want the record in pair with Episode start and Episode end if pair doesn't exists then want to exclude the record.

 

Thank you in advance.

Hi @P_P2595 🙂
To achieve your goal you can follow the steps in PQ :

1. Add a conditional column that checks if the concatenated column that we added has a pair "....Started-...Ended"

Ritaf1983_0-1684745023331.png

2. Filter your table by the result:

Ritaf1983_1-1684745075675.png

3. Delete unnecessary column of condition and close and apply

Ritaf1983_2-1684745160118.png

Result :

Ritaf1983_3-1684745223623.png

I also updated a sample file on the previous link

 

If my answer was helpful please give me a Kudos and accept as a Solution.

 

Thank you Ahmedx for your quick response, but removing duplication won't work for me. 

you can see the input and output for id 2 there are two episode start and two episode end and I don't want to remove any of them because it's with pair of Episode Start and Episode End.

 

In short, I want to exclude the record if there is only Episode start or Episode End (ex. id 5, last row of id 4,3rd row of id 1)

Thank you.

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.