coalesce across rows, not columns

[AndrewPF]

My data consists of (1) assessment ID (2) assessment name (3) question number (4) target score and (5) actual score. 

I have some reassessments on which only specific questions have been answered - i.e. those which were under target in the previous assessment. 

How can I create a data set which shows new questions only where they have been replaced by existing previous ones? 

Example: 

Actual data: 

Assessment ID	Name	Question no.	Target score	Actual score	Notes
123	Initial	1	3	3	on target - no reassessment required
123	Initial	2	4	3	not on target
123	Initial	3	5	5	on target - no reassessment required
123	Initial	4	2	1	not on target
123	Initial	5	3	3	on target - no reassessment required
245	Reassessment	2	4	4
245	Reassessment	4	2	2

Required data: 

Assessment ID	Name	Question no.	Target score	Actual score	Notes
123	Initial	1	3	3
245	Reassessment	2	4	4	reassessment - superceded previous answer
123	Initial	3	5	5
245	Reassessment	4	2	2	reassessment - superceded previous answer
123	Initial	5	3	3

This problem is perfectly clear in my own head, I just hope I have effectively conveyed it here! 

[AndrewPF]

Hi, 

thanks for the response. 

I probably should have clarified in my original post, but my data is more complicated than in the example.  Aside from lots of other columns (which I think I can deal with), I have up a business ID, for which we can have any number of assessments (we're currently at a maximum of 6 for any one business ID). 

So I wonder - if I group by business ID and question number, would that still work? 

[dufoq3 · Accepted solution]

@AndrewPF it should work. Edit GroupedRows step to something like this:

GroupedRows = Table.Group(ReplaceNotes, {"business ID" ,"Question no."}, {{"Detail", each _, type table}, {"Result", each Table.LastN(_, 1) }}),

If it won't work just send new sample data and I'll try to help you.

[dufoq3]

Hi @AndrewPF,

Result:

I expect that Reassesment (for same question) is everytime below the Initial in your table (let me know if it isn't)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJR8szLLMlMzAGyDIHYGIrz8xRKEovSU0sUdBXy8hWKUhOLi1OLi3NT80qAnMLSzKLUFKVYHUxDjIDYBGpIXn6JAtwgrKpBPFMoJttKE6i1hkRZaUqOL41MQNqCkOSRvArCCriVwZxnBFEWCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Assessment ID" = _t, Name = _t, #"Question no." = _t, #"Target score" = _t, #"Actual score" = _t, Notes = _t]),
    ReplaceNotes = Table.ReplaceValue( Source,
      each [Name],
      null,
      (x,y,z)=> if y = "Reassessment" then "reassessment - superceded previous answer" else null,
      {"Notes"} ),
    GroupedRows = Table.Group(ReplaceNotes, {"Question no."}, {{"Detail", each _, type table}, {"Result", each Table.LastN(_, 1) }}),
    CombinedResult = Table.Combine(GroupedRows[Result])
in
    CombinedResult