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

[v-pagayam-msft · Accepted solution]

Hi @WMart_AMS ,
May I ask if you have gotten this issue resolved?
If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Thank you.

[v-pagayam-msft]

Hi @WMart_AMS ,
Thank you @bhanu_gautam  and @SundarRaj for the prompt response!

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.If our community member's response answer resolved your query, please mark it as "Accept Answer" and select "Yes" if it was helpful.If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[SundarRaj]

@WMart_AMS , another solution you could possily look at. I'll leave the images of the source and output and code used. Thanks!

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZLLEcAgCER78cxBQGJ6yaT/NqLGfGQ5srvygPE4EidKVXPhnLn2QtJJnyxNt63LVMBQaQVjXluRMf3J4qG6yD8oGgP6TCMxVmKswq6zjSJYwFjAGoM1BhcPZrLFeMFMGxg3GPPY32EtPrPhthWMue2OL0b/OaZ5sFD7D+cF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Project_Code = _t, Overall_Avg_Score = _t]),

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Project_Code", Int64.Type}, {"Overall_Avg_Score", Int64.Type}}),

#"Grouped Rows" = Table.Group(#"Changed Type", {"Index"}, {{"All", each _[Overall_Avg_Score], type table [Index=nullable number, Project_Code=nullable number, Overall_Avg_Score=nullable number]}}),

Custom1 = Table.AddIndexColumn(#"Grouped Rows","Overall_Avg_Score_Next",0,1),

Custom2 = Table.TransformColumns(Custom1, {"Overall_Avg_Score_Next", each try Custom1[All]{_ - 1} otherwise {null}})[[All],[Overall_Avg_Score_Next]],

Custom3 = Table.AddColumn(Custom2,"List", each Table.FromColumns(Record.ToList(_)))[[List]],

Custom4 = Table.AddIndexColumn(Custom3,"Index",0,1),

#"Reordered Columns" = Table.ReorderColumns(Custom4,{"Index", "List"}),

#"Expanded List" = Table.ExpandTableColumn(#"Reordered Columns", "List", {"Column1", "Column2"}, {"Column1", "Column2"}),

Custom5 = Table.AddIndexColumn(#"Expanded List","Project_Code",0,1),

Custom6 = Table.TransformColumns(Custom5,{"Project_Code", each #"Changed Type"[Project_Code]{_}}),

#"Reordered Columns1" = Table.ReorderColumns(Custom6,{"Index", "Project_Code", "Column1", "Column2"})

in

#"Reordered Columns1"

The Source is basically me entering the same data manually through the "Enter Data" tab in Power Query UI

[bhanu_gautam]

@WMart_AMS Go to the "Add Column" tab and click "Custom Column".
Use the following formula to create the "previous Index Overall_avg_Score" column:

= try Table.AddColumn(#"Previous Step", "previous Index Overall_avg_Score", each if [Index] = 1 then null else Table.SelectRows(#"Previous Step", each [Index] = _[Index] - 1 and [Project_Code] = _[Project_Code])[Overall_Avg_Score]) otherwise null

Replace #"Previous Step" with the actual name of the previous step in your query.
This formula checks if the current index is 1, in which case it returns null (since there is no previous index). Otherwise, it selects the row where the index is one less than the current index and matches the Project_Code, then retrieves the Overall_Avg_Score from that row.

[WMart_AMS]

This doesn't seem to work. I used the code below. Could it be that there is something missing in the part that i marked with Bold text? It seems to have a problem with 'otherwise'

= Table.AddColumn(#"Replaced Value3", "PrevOverallScore", each if [Index] = 1 then null else Table.SelectRows(#"Replaced Value3", each [Index] = _[Index] -1 and [Project_code] = _[Project_code]) [Overall_Avg_Score] otherwise null

[WMart_AMS]

i copied your exact code. This time it does not give an error message, but it only shows null values. Any other suggestions or additions? 

[bhanu_gautam]

Did you have index column if not add one

[WMart_AMS]

Yes, i already have an index column as you can find in my example data. Do you have another way?

[bhanu_gautam]

Use this

Table.AddColumn(
#"Replaced Value3",
"PrevOverallScore",
each if [Index] = 1 then null else
let
prevRow = Table.SelectRows(#"Replaced Value3", each [Index] = _[Index] - 1 and [Project_code] = _[Project_code])
in
if Table.IsEmpty(prevRow) then null else prevRow[Overall_Avg_Score]
)