Forum Discussion
Multiple Rows transpose to Column
Honestly, there's a lot going on in your M code—way more than what was initially asked. If I were applying the code I shared across multiple files (assuming the sources are Excel workbooks), I’d first connect to each file using Excel.Workbook in a custom column, expand that column, select the needed sheets or tables, and then apply the logic I gave you—only to the actual data, not the Excel binary files.
Super UserHello Anonymous
try this
Select the top two header rows (first two rows), and Transpose the table (Home → Transform → Transpose).
Now you will see two columns:
Column1 → Previous first-row header (Fruits, Fruits, Snacks, Snacks)
Column2 → Previous second-row header (Apple, banana, Chips, Popcorn)
Select both these columns and Fill Down/Fill Up if needed.
Select these two columns, and then the rest (the value columns), and Unpivot Other Columns.
Rename
Attribute → Value
The other two columns → Category1, Category2
Thanks,
Pankaj Namekar | LinkedIn
If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
how can I select 2 rows at once? not selectable.