Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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 :
If my answer was helpful please give me a Kudos and accept as a Solution.
pls try this
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 :
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 | |||
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.
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.
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.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |