 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.

6 Replies

# Re: Power Query Formulas

best response confirmed by AFFCF_Grants_Admin (New Contributor)
Solution

# Re: Power Query Formulas

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 [
_,
"Test Number", 1, 1, Int64.Type
),
TestNumber,
"Diff2-1",
each
if [Test Number] = 2
then TestNumber[Score]{1}-TestNumber[Score]{0}
else null
),
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"``````

# Re: Power Query Formulas

Thank you.  I will check this out tonight.

# Re: Power Query Formulas

Thank you.  I will check this out tonight.

# Re: Power Query Formulas

This works perfectly.

Thank you very much!!!