Forum Discussion

AndrewPF's avatar
AndrewPF
Helper V
2 years ago
Solved

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...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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's avatar
dufoq3
Community Champion
2 years ago

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