Get value from previous grouped index number in a new column (Powerquery or DAX)

[WMart_AMS]

Hi!

Can somebody help me out with a solution to generate the column "previous Index OVerall_avg_Score" as presented in the image below. Preferably i'd like to get this result in PowerQuery, but DAX solutions are also welcome. The dataset is presented below the image.

I've looked up various solutions, but they are mainly focused on date-related measures or they do not work with a group of index numbers as shown in this example. Your help is greatly appreciated!

Index	Project_Code	Overall_Avg_Score
1	7304100171	2
1	7304210056	2,4
1	7304210032	1
1	7304210053	0
1	7304210033	0
2	7304100171	3
2	7304210056	2,3
2	7304210032	2,4
2	7304210053	0
2	7304210033	0
3	7304100171	2,4
3	7304210056	2,2
3	7304210032	2,4
3	7304210053	0
3	7304210033	0
4	7304100171	1,5
4	7304210056	1,6
4	7304210032	2
4	7304210053	0
4	7304210033	0
5	7304100171	3
5	7304210056	2,7
5	7304210032	2,8
5	7304210053	0,6
5	7304210033	2,1

[Cookistador · Accepted solution]

I had to do something similar a few months ago

With Power query:

You create a column called PreviousIndex: Index -1

Then you make a self join using Index & Project Code AND PreviousIndex &  Project Code

In dax:

You create a calculated columns with a PreviousIndex, you create two calculated column in your source table:  Index & Project Code  and in the calculated column: reviousIndex &  Project Code

You link your two tables and then you use 

NATURALLEFTOUTERJOIN(SourceTable, CalculatedTable)

[WMart_AMS]

Thanks so much, the PowerQuery solution you provided worked! This issue bothered me for a long time already. Thank you so much!