Feb 11 2021 12:01 PM
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.
Feb 11 2021 12:30 PM
@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/
Feb 11 2021 12:54 PM
SolutionIt'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"
Feb 11 2021 01:47 PM
Feb 11 2021 01:48 PM
Feb 13 2021 03:00 PM
Feb 11 2021 12:54 PM
SolutionIt'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"