Forum Discussion
coalesce across rows, not columns
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!
3 Replies
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 CombinedResultHi,
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?
