Forum Discussion
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.
- Yes! You are the bee's knee, I wouldn't have found this on my own! Thank you!!!
Out of curiosity I looked at doing it without Indexing nor Pivoting. Best I could come up with so far:
// Alternative let Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content], GroupedRowsOne = Table.Group(Source, {"Category", "Class", "Function"}, {"CAPABILITIES", each [Capability], type list}, GroupKind.Local // [Category], [Class] & [Function] ordered in Source table ), GroupedRowsTwo = Table.Group(GroupedRowsOne, {"Category", "Class"}, {"FUNCTIONS", each Table.FromColumns([CAPABILITIES],[Function]), type table}, GroupKind.Local ), ExpandedFunctions = Table.ExpandTableColumn(GroupedRowsTwo, "FUNCTIONS", List.Sort(List.Distinct(GroupedRowsOne[Function])) ) in ExpandedFunctions
Hi Mr_Gou
Interesting scenario. I doubt there's a way to do it with the UI/Menus only...
In attached file all steps are done with the UI but Grouped Rows that's been altered with the Advanced Editor to add an [INDEX] column to each nested table
Re. the Pivoted Column step the "trick" is - when doing it with the UI:
