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.

AndrewPF's avatar
AndrewPF
Helper V
2 years ago

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's avatar
    dufoq3
    Community Champion
    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.