Power Query: Transpose part of a table
Suppose I have a table like this:
+------------+---------+------------+-------------------------------------------------------+
| Category | Class | Function | Capability |
+------------+---------+------------+-------------------------------------------------------+
| Category 1 | Class 1 | Function 1 | A first capability |
+------------+---------+------------+-------------------------------------------------------+
| Category 1 | Class 1 | Function 1 | Another capability |
+------------+---------+------------+-------------------------------------------------------+
| Category 1 | Class 1 | Function 2 | A different capability |
+------------+---------+------------+-------------------------------------------------------+
| Category 1 | Class 2 | Function 1 | Something completely different |
+------------+---------+------------+-------------------------------------------------------+
| Category 1 | Class 2 | Function 2 | This parrot is no more |
+------------+---------+------------+-------------------------------------------------------+
| Category 2 | Class 3 | Function 1 | Nobody expects the Spanish inquisition |
+------------+---------+------------+-------------------------------------------------------+
| Category 2 | Class 3 | Function 2 | We used to live in an old water tank on a rubbish tip |
+------------+---------+------------+-------------------------------------------------------+
| Category 2 | Class 4 | Function 1 | Have you got anything without spam? |
+------------+---------+------------+-------------------------------------------------------+
| Category 2 | Class 4 | Function 1 | We're the People's Front of Judea |
+------------+---------+------------+-------------------------------------------------------+How can I transform it to get this:
+------------+---------+----------------------------------------+-------------------------------------------------------+
| Category | Class | Function 1 | Function 2 |
+------------+---------+----------------------------------------+-------------------------------------------------------+
| Category 1 | Class 1 | A first capability | A different capability |
+------------+---------+----------------------------------------+-------------------------------------------------------+
| Category 1 | Class 1 | Another capability | |
+------------+---------+----------------------------------------+-------------------------------------------------------+
| Category 1 | Class 2 | Something completely different | This parrot is no more |
+------------+---------+----------------------------------------+-------------------------------------------------------+
| Category 2 | Class 3 | Nobody expects the Spanish inquisition | We used to live in an old water tank on a rubbish tip |
+------------+---------+----------------------------------------+-------------------------------------------------------+
| Category 2 | Class 4 | Have you got anything without spam? | |
+------------+---------+----------------------------------------+-------------------------------------------------------+
| Category 2 | Class 4 | We're the People's Front of Judea | |
+------------+---------+----------------------------------------+-------------------------------------------------------+I've been playing in Power Query but haven't been able to find the right tool. I'm sure there's a simple solution, but I can't get to it!
Thanks!
R.
