Power Query Formulas

[AFFCF_Grants_Admin]

I have a table that shows students Col A, test date Col B, and test score Col C.

After I sort by student-asc, then test date-asc, the formula =IF(A2=A1,+D1+1,1) in column D tells me what how many tests the student has taken.

Column E shows the difference between test 2 and test 1 if the row is the second test for the student.  =IF(D3=2,+C3-C2,"")

Column F shows the difference between test 3 and test 1 if the row is the third test for the student.  =IF(D3=3,+D3-D1,"")

When I copy the formulas, the work as expected because the cell references are not anchored.

I’m trying build this in power query, and I can get the first three columns without a problem, but I’m having a devil of a time trying to figure out how to add the formulas. I know it's Add Column, but how to reference the row above the current row to evaluate if the student is the same or not.

Likewise, I can’t figure out how to reference 1 row above if it’s test 2 and 2 rows above if it’s test 3.

Any help on this would be appreciated.

[#2]

@AFFCF_Grants_Admin Perhaps easiest to refer to one of many on-line resources that address your question. 

https://www.goodly.co.in/refer-previous-row-next-row-power-query/ 

[#3 · Accepted solution]

@AFFCF_Grants_Admin 

It's not necessary and better not to repeat Excel formulas literally. Group records by names without aggregation and within it work with each name as with separate table. Add Index as Test Number, with that for second row take difference in Score between it and Score in first row; in next column same for the third row. Please note in Power Query numbering starts from zero.

You may do latest transformations on separate query with only one name, after that copy/paste code under the grouping, here is only to change table name. Entire script looks like

let
    Source = Excel.CurrentWorkbook(){[Name="Tests"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(
        #"Changed Type",{{"Youth", Order.Ascending}, {"Date", Order.Ascending}}
    ),
    #"Grouped Rows" = Table.Group(
        #"Sorted Rows",
        {"Youth"},
        {{
            "Group",
            each [
                TestNumber = Table.AddIndexColumn(
        _,
        "Test Number", 1, 1, Int64.Type
    ),
    Diff2 = Table.AddColumn(
        TestNumber,
        "Diff2-1",
        each
            if [Test Number] = 2
            then TestNumber[Score]{1}-TestNumber[Score]{0}
            else null
    ),
    Diff3 = Table.AddColumn(
        Diff2,
        "Diff3-1",
        each
        if [Test Number] = 3
        then Diff2[Score]{2}-Diff2[Score]{0}
        else null
    ) ][Diff3]
        }}
    ),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Youth"}),
    #"Expanded Group" = Table.ExpandTableColumn(
        #"Removed Columns",
        "Group",
        {"Youth", "Date", "Score", "Test Number", "Diff2-1", "Diff3-1"},
        {"Youth", "Date", "Score", "Test Number", "Diff2-1", "Diff3-1"}
    )
in
    #"Expanded Group"

[#4]

@Riny_van_Eekelen 

Thank you.  I will check this out tonight.

[#5]

@Sergei Baklan 

Thank you.  I will check this out tonight.

[#6]

@Sergei Baklan 

This works perfectly.

Thank you very much!!!

[#7]

@AFFCF_Grants_Admin , you are welcome