Shift Columns (different amount every time) into rows

[jacobbarclay0]

Hey everyone,

I am attempting to shift dynamic columns I parse from an API pull into new rows. In the data vector, some pulls may have 1 value and time (1 line) and some have 500 lines (listed below shows 2 lines, in bold). Below is a simplified example of what is happening and what I want.

API Pull:

<Identifier>
   <ID_Value>0001</ID_Value>
</Identifier>
<Data>
   <DataVector>
      <Data Value="5" Time="09/01/2017"/>
      <Data Value="10" Time="10/01/2017"/>
   </DataVector>
</Data>

Outcome:

0001

5

09/01/2017

10

10/01/2017

Above is not how I want it structured. Below is the desired outcome. How might I do this?

0001	5	09/10/2017
0001	10	10/01/2017

[danrh · Accepted solution]

I'd give something like this a try:

The Transpose puts the values vertically, then I label every other record to pull out dates vs values and flip them back horizontally, but stacked instead of side by side.  This should scale to however many entries you have.