Forum Discussion

AFFCF_Grants_Admin's avatar
AFFCF_Grants_Admin
Copper Contributor
Feb 11, 2021
Solved

Power Query Formulas

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.

Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 11, 2021

    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"

     

6 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 11, 2021

    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"

     

Resources