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.
Hey there,
I have following situation which I couldn't resolve yet:
I want to compare each row with the previous row.
If Colour and Previous Colour are both the same as in the row before I want to skip to the next row automatically and try it again.
If they are not the same, in an additional column it should add the value "1"
ID | Colour | Previous Colour | Index |
126443 | Red | Green | 1 |
123456 | Blue | Red | 2 |
175332 | Blue | Red | 3 |
765432 | Yellow | Blue | 4 |
733511 | Green | Yellow | 5 |
531534 | Blue | Green | 6 |
Something like:
if ((PrevColour(index-1) = PrevColour(index) && Colour(index-1) = Colour(index))
then jump to index+1
else type "1" in new column
I'd appreciate any help. Kind regards 🙂
Jama1234
Solved! Go to Solution.
1. Add a new column: Index2 as an index column starting at 2 (one more than column Index)
2. Do a self join (Merge Tables) with Index and Index2 as the key joining columns. Kind of join is left outer
3. Expand the newly generated column to new rows.
4. Now you have duplicate columns in your table with values equal to the previous row.
5. Add a column that compares the current values with the previous values that are stored in the same row.
6. Remove unneeded columns
1. Add a new column: Index2 as an index column starting at 2 (one more than column Index)
2. Do a self join (Merge Tables) with Index and Index2 as the key joining columns. Kind of join is left outer
3. Expand the newly generated column to new rows.
4. Now you have duplicate columns in your table with values equal to the previous row.
5. Add a column that compares the current values with the previous values that are stored in the same row.
6. Remove unneeded columns
It worked really well.
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.