Hello
I'm struggling to get power query to transpose / pivot this (dummy survey data, 1 row per question per user):
...into one row per user, like this:
Can anybody help?
In case it helps, here is the source dummy data in M:
I'm struggling to get power query to transpose / pivot this (dummy survey data, 1 row per question per user):
| UserID | Catergory | Question No. | Question | Answer | Answer Score | Answer Band |
| 1200 | General | 1 | Have you done X? | Yes | 1 | None |
| 1200 | General | 2 | Have you done Y? | No | 3 | Low |
| 1200 | General | 3 | Which do you prefer? | This | 10 | High |
| 1200 | Part 1 | 4 | Which do you prefer? | That | 6 | Medium |
| 1200 | Part 1 | 5 | What is this? | Something | 4 | Medium |
| 1200 | Part 2 | 6 | Which do you prefer? | Else | 0 | None |
| 1200 | Part 2 | 7 | Have you done X? | Yes | 4 | Medium |
| AXLF | General | 1 | Have you done X? | Yes | 1 | None |
| AXLF | General | 2 | Have you done Y? | Yes | 2 | Low |
| AXLF | General | 3 | Which do you prefer? | This | 10 | High |
| AXLF | Part 1 | 4 | Which do you prefer? | This | 8 | Medium |
| AXLF | Part 1 | 5 | What is this? | That | 4 | Medium |
| AXLF | Part 2 | 6 | Which do you prefer? | That | 3 | Low |
| AXLF | Part 2 | 7 | Have you done X? | Something | 3 | Low |
| OO567 | General | 1 | Have you done X? | Yes | 1 | None |
| OO567 | General | 2 | Have you done Y? | Else | 0 | None |
| OO567 | General | 3 | Which do you prefer? | No | 8 | Medium |
| OO567 | Part 1 | 4 | Which do you prefer? | No | 2 | Low |
| OO567 | Part 1 | 5 | What is this? | Something | 4 | Medium |
| OO567 | Part 2 | 6 | Which do you prefer? | That | 8 | Medium |
| OO567 | Part 2 | 7 | Have you done X? | That | 6 | Medium |
...into one row per user, like this:
| UserID | Q. 1 answer | Q. 2 answer | Q. 3 answer | Q. 4 answer | Q. 5 answer | Q. 6 answer | Q. 7 answer | Q. 1 Score | Q. 2 Score | Q. 3 Score | Q. 4 Score | Q. 5 Score | Q. 6 Score | Q. 7 Score | Q. 1 Rating | Q. 2 Rating | Q. 3 Rating | Q. 4 Rating | Q. 5 Rating | Q. 6 Rating | Q. 7 Rating |
| 1200 | Yes | No | This | That | Something | Else | Yes | 1 | 3 | 10 | 6 | 4 | 0 | 4 | None | Low | High | Medium | Medium | None | Medium |
| AXLF | Yes | Yes | This | This | That | That | Something | 1 | 2 | 10 | 8 | 4 | 3 | 3 | None | Low | High | Medium | Medium | Low | Low |
| OO567 | Yes | Else | No | No | Something | That | That | 1 | 0 | 8 | 2 | 4 | 8 | 6 | None | None | Medium | Low | Medium | Medium | Medium |
Can anybody help?
In case it helps, here is the source dummy data in M:
Power Query:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZQ7b8IwEID/ipWZIRgIbKgDJQNtKlEJEGKw4Eos5VHlAeLf9+y6VcAXN83g2LL13dn32dnvvSH3fW/gLSGDQiQ4GmILxQXYLa/ZKc+Abec4tYPSLL7inHcYECi30N1cA/gZYVvlVxpUi5tYHmOkNPxZwAcUCn6PpU6sgFCe42aAN1FUTG1p7OJFhV2A7QVOsk7JABMdQFRMlqzCjIpc5yngODubBC04N9Fb8i+SErDzicL94lNXyR9SP21Xzz19WWiLr2+UN4RZ5H+FmQDdhGl+Rh7bKcyopgvWxZUJMLJP/peq5l1p4lE0CaY9ddlsiy/qitmwQ5l+oA8F/wnQRZnmOXHsvu/rju8mzbV9hzji93D4Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, Catergory = _t, #"Question No." = _t, Question = _t, Answer = _t, #"Answer Score" = _t, #"Answer Band" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", type text}, {"Catergory", type text}, {"Question No.", Int64.Type}, {"Question", type text}, {"Answer", type text}, {"Answer Score", Int64.Type}, {"Answer Band", type text}})
in
#"Changed Type"
