Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
39 | |
24 | |
21 | |
21 | |
13 |