Conditional formatting based on different columns

[Diego11]

Hi everyone,

 I have different clients, and I need to compare their scores from stage 1 with the scores from the latest stage they have recorded. The problem is that each stage is recorded in a new column and I don't know how to put a conditional formula between different columns, something like " if stage 3 exist, compare stage 3 with 1,  else if stage 2 exist compare stage 2 with stage 1, else null ) 

E.g.

Initial table 

User ID	Stage	Score
001	1	50%
001	2	30%
001	3	70%
002	1	15%
002	2	27%
003	1	10%
003	2	66%
003	3	30%
004	1	10%
005	1	26%
005	2	37%

 Result

User ID	Diff. Score
001	20%
002	12%
003	20%
004	null
005	11%

Does anyone have an idea of how I can do it?

I hope my explanation was clear,

Thank you so much in advance,

[mussaenda · Accepted solution]

Hi @Diego11,

I did your requirement in Power query.

Attached is the pbix

hope this helps

[Diego11]

Hi @mussaenda ,

 This helped me a lot! Thank you so much for your help