Forum Discussion

Mr_Gou's avatar
Mr_Gou
Copper Contributor
Nov 15, 2023

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.

    • Mr_Gou's avatar
      Mr_Gou
      Copper Contributor
      Yes! You are the bee's knee, I wouldn't have found this on my own! Thank you!!!
      • Lorenzo's avatar
        Lorenzo
        Silver Contributor

        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

         

         

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    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:

Resources