Need help with pivoting the data horizontally

Question

Hello, team

I need your help to transform the data from an Excel report I have. The attached screenshot 1 shows the data how it is currently in my report.

The screenshot 2 shows how I would like the data to look like.

I tried to use the Cross tab but I am really not sure how to get the data to look like in screenshot 2.

Can you please help me?

Thank you!

Kind regards,

Ioana

Screenshot1_22OCT2025.png

Screenshot2_22OCT2025.png

davidskaife · Accepted Answer

Hi @ivoiculescu2020

Here is a solution that works with the data you have provided, its not dynamic though so assumes nothing changes..

You have to split the data into two, get the header into the right layout first, then union in the actual data:

Split the data into two streams using Sample tool, restricting to first 13 rows, and skipping first 14 rows (to ignore the null row). Cross Tab both streams, and for the header stream use the Dynamic Rename tool to take the Headers from the first row of data. Then remove the columns you won't need, and union by position to get the completed output.

davidskaife · Accepted Answer

Hi @ivoiculescu2020

Edit: removed my original response as realised what you meant - let me work up a second solution to handle multiple records!

What would separate the records, a space?

davidskaife · Accepted Answer

Hi @ivoiculescu2020

I've updated the workflow example to account for multiple records, where they are separated by a null row:

What it will do is generate specific RecordID's and Record Numbers per set of data, to allow for aligning and grouping correctly,

If you let me know if the separator between records is different i can update!