Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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.
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?
@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.
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
Check out the July 2025 Power BI update to learn about new features.