Forum Discussion
Solved
Transpose or ??
This is probably a simple thing, but I haven't found anything that works. It's similar to transpose, but not really. So basically I need the table on the left of the screenshot converted into the table on the right. The end result would have the category only listed once and the variations of that category shown to the right in columns. I have a list of about 5k items so I was just looking for a speedy solution. Thanks for any ideas!
(EDITED: Added Option3 that's almost all done with the UI/Menus)
With Power Query
2 options in attached file. Option1_1 is a variation of Option1, in case you hit performance issue with the latter
Option1:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], GroupedRows = Table.Group(Source, {"Category"}, {"DATA", each Table.FromRows( { {Table.FirstValue(_)} & [Variation] } ), type table } ), CombinedTables = Table.Combine(GroupedRows[DATA]) in CombinedTables- Maybe I missed it, but I don't see the different options in the file. Thanks
- It looks like you're trying to "pivot" the data, as opposed to transpose it.
That's a job for Power Query. It depends on your Excel version / platform. Perhaps Power Query is preferable solution if you have few thousand rows of data. For Excel 365 dynamic arrays formulae could work, but that again depends on version.
So, to be more concrete it's desirable to know on which excel you are.
SergeiBaklan I am using a windows PC with Office 365. I’m somewhat familiar with Power Query but just didn’t know where to start.
