Power Query: Transpose part of a table

[Mr_Gou]

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.

[#2]

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:

[#3 · Accepted solution]

@Mr_Gou 

On reflection... Doable with the UI/Menus only. In attached file see extra step Added Custom after Grouped Rows (not altered this time)

[#4]

Yes! You are the bee's knee, I wouldn't have found this on my own! Thank you!!!

[#5]

Glad this helped & Thanks for providing feedback @Mr_Gou 

Nice EOD...

[#6]

@Mr_Gou 

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