Horizontal/Vertical Data

I have been using a messy query report for over 6 months and am finally convinced this was never my best approach. So this post is to ask the community for feedback.

I manage a small company that supports people with developmental disabilities. I pull medical information daily on hundreds of individuals for my managers to review. The attached is a small sample of a report but there are actually 60 categories (columns) , which always remain the same. Report pulls data from an external source via power query. Horizontal Report can be difficult to read since its runs so many columns across. So I transpose it to a Vertical layout in power query. Of course this has caused lots of issues upon refresh with formatting, which does not always carry over to new columns.

Questions:
1. Is transposing data in power query the best approach to get my data to lay out vertically?
2. Would pivot table produce similar/better results?
3. Is there a way to carry over formatting rules to new adjacent columns after refresh.

Of course all solutions must be dynamic since hundreds of entries change often. Thanks so much for all responses!

With O365, using PQ (in my opinion) is crazy (unless i have misunderstood!!). You can do it all with a one-cell formula:

In B1:

=TRANSPOSE(Horizontal!A2:K5)

Job done...

Did you even open the file I attached? The formula delivers the vertical format!!!! A Named range can be used to select the appropriate number of rows in horizonatal format....

I'm away fom the PC now for 10 hours or so.. However, you can copy/paste formatting from columnB to wherever you expect the data to end. Do that... then add more rows.
Until Named ranges are added, you'll have to change the formula range manually.

So.

1. I turned the data in horizontal into a Table.

2. I formatted columnc C to AZ to match column B.

3. I formatted the comments row as : 0;-0;;@ to hide zeros when comments are absent.

4. The formula (which now resides in VERTICAL A1) reads:
=TRANSPOSE(Table1[#All])

Fully dynamic. Formats perfectly. Try it.