Forum Discussion
Have each value in many dynamic columns stacked into one column
Hello,
I have an Excel table that is like this:
| Category | Sub-category | Sub-sub-category | Product 1 | Product 2 | Product 3 |
| Vegetables | Green vegetables | Imported vegetables | Cabbage | Kale | |
| Fruits | Tropical fruits | Imported from Brazil | Coconut | ||
| Vegetables | Root vegetables | Domestic vegetables | Potato | Tomato | Carrot |
I want to have the outcome table like this:
| Category | Sub-category | Sub-sub-category | Product |
| Vegetables | Green Vegetables | Imported vegetables | Cabbage |
| Vegetables | Green Vegetables | Imported vegetables | Kale |
In the future, we will have plenty more products, so there will be more columns for example Product 4, Product 5, Product 6, Product 8, etc. I also want all the columns that start with "Product" be stacked this way into one column Product.
How can I achieve this?
Thank you beforehand
Hi Anonymous ,
Multi-select your [Category], [Sub-category], and [Sub-sub-category] fields.
Go to Transform tab and select the dropdown button next to 'Unpivot Columns'.
Select 'Unpivot Other Columns' from the dropdown list.
this gives me the following output:
You can then remove the [Attribute] field and filter out null values from the [Value] field and rename fields as appropriate.
Pete
2 Replies
- BA_Pete
Super User
Hi Anonymous ,
Multi-select your [Category], [Sub-category], and [Sub-sub-category] fields.
Go to Transform tab and select the dropdown button next to 'Unpivot Columns'.
Select 'Unpivot Other Columns' from the dropdown list.
this gives me the following output:
You can then remove the [Attribute] field and filter out null values from the [Value] field and rename fields as appropriate.
Pete