Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |