Pivoting records

Power Query
1

I have some data where the records are in a vertical format. I have been trying various pivots and transpositions.

I have attached a screenshot with an example where the second data record starts four up from the bottom.

The required column headers are in the middle row and the data fields in the right.

Any help greatly appreciated.

Paul

Vertical Data Records
Vertical Data Records414Ă—564 68.2 KB

1 Like
2

Hi @PaulBoyes,

We had a similar challenge in POTW #2 where the data was also in a stacked format.
I hope you’ll find this video helpful

.
If you need more assistance please provide a sample file

2 Likes
3

Hi Paul,

Good news. It will only take you one step in the query editor.

  1. Select the second column (i.e. the one we want to “columnize”)
  2. Go to transform - pivot column (check my screenshot below to see which button)
  3. Choose column 3 as the value column
  4. Choose no aggregation

See screenshot below (sorry it’s in dutch!)

Steps:

image
image1651Ă—608 43.3 KB

Result (only tested for a small subset of your data)

image
image897Ă—132 6.29 KB

4

Hi @PaulBoyes , did the response provided by @Melissa and @Rens help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

5

Hi @PaulBoyes, we’ve noticed that no response has been received from you since the 25th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.