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

[P_P2595]

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.

[Ritaf1983 · Accepted solution]

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:

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

1. Group by:

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

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

 

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

Result :

Link To sample file 

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

[Ahmedx · Accepted solution]

pls try this

[Ritaf1983 · Accepted solution]

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:

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

1. Group by:

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

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

 

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

Result :

Link To sample file 

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

[P_P2595]

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
Id	Description		Id	Description
1	Episode Start		1	Episode Start
1	Episode End		1	Episode End
1	Episode Start		2	Episode Start
2	Episode Start		2	Episode End
2	Episode End		2	Episode Start
2	Episode Start		2	Episode End
2	Episode End		3	Episode Start
3	Episode Start		3	Episode End
3	Episode End		4	Episode Start
3	Episode End		4	Episode End
4	Episode Start
4	Episode End
4	Episode Start
4	Episode Start
5	Episode Start
5	Episode 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.

[Ritaf1983]

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"

2. Filter your table by the result:

3. Delete unnecessary column of condition and close and apply

Result :

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.

[P_P2595]

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.

[Ahmedx]

plse see my video

https://1drv.ms/v/s!AiUZ0Ws7G26RiCn1_MuesF7dBZJq?e=MFoz67

Sample PBIX file attached

How to concat text values.pbix