Transpose rows, distributing them in columns? / Transpor linhas, distribuindo-as em colunas?

New Contributor

My question is the following, I need to distribute this data below each column. But when I use "Transpose" they are next to each other, I would like them to be one below the other, as suggested in the attached image:


A minha dúvida é a seguinte, preciso distribuir estes dados para abaixo de cada coluna. Mas quando uso o "Transpor" elas ficam uma ao lado da outra, eu gostaria que ficasse um abaixo do outro, como sugere a imagem em anexo:


8 Replies

Hi @luccagrtt501st 


One way with Get & Transform aka Power Query (file attached)



Alternatively, with formula, assuming you run Excel 2021/365:


  NoBlank, FILTER(Table1[Column2],Table1[Column2]<>""),
  INDEX(NoBlank, SEQUENCE(ROWS(NoBlank)/7,7))


@L z. 

Thank you very much. It worked really well!
Just a problem, I have no idea how you created this... I just copied the data I needed and used your file as a Template, I would like to know how you set it up

Are you talking about the Power Query option?

@L z. 

Yes! I would like to know how you did each step, some buttons I couldn't find, I spent almost 1 hour looking, so as not to bother you



best response confirmed by luccagrtt501st (New Contributor)


Steps TableSplit through CombinedTables cannot be done with the Power Query User Interface, you have to implement them with the Advanced Editor


In the attached file I updated the query to add some comments (lines start. with //), this gives:

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // Remove empty rows:
    RemovedNull = Table.SelectRows(Source, each ([Column2] <> null)),
    // Split the table every 7 rows ==> A list of Tables:
    TableSplit = Table.Split(RemovedNull, 7),
    // Iterate over the list to Transpose each item (each Table):
    TransposedEachTable = List.Transform(TableSplit, Table.Transpose),
    // Combine list items as a Table:
    CombinedTables = Table.Combine(TransposedEachTable)


Power Query doc. is available here. Any specific question re. the above query let me know

And good luck with PQ, learning it is a journey...

I figured I wouldn't have to learn about the language/Advanced Editor... It's going to be a job.
But you really helped me a lot!
Hugs from Brazil!
Glad I could help & Thanks for providing feedback