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

[luccagrtt501st]

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:

[#2]

Hi @luccagrtt501st 

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

[#3]

@luccagrtt501st 

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

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

[#4]

@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

[#5]

Are you talking about the Power Query option?

[#6]

@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

[#7 · Accepted solution]

@luccagrtt501st 

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:

let
    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)
in
    CombinedTables

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...

[#8]

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!
Thanks!
Hugs from Brazil!

[#9]

Glad I could help & Thanks for providing feedback