I took a try at it. Not sure if this quite got there,or if it will scale appropriately.
And probably there are more straightforward ways to achieve it.
Basically, I had to do two consecutive pivots.One wildcard pivot to get all the values.
Then another wildcard pivot to get the column headings.
Then used a filter on the Tableau side to match things up:
[Pivot1 Names - 1]=LEFT([Label2],6)
and lastly an Index to get things aligned.
Please see tflx and twbx attached in the Forum Thread:
Here's a flow that generates the desired output and should dynamically extend to handle additions of ...Value4, ...Value5, etc. fields:
The only thing missing from the flow is that the final data types aren't set properly because the rows to columns pivot at the end is setting everything to text. If you're using a Hyper output you'll have to manually change those, or if you export to CSV and then load into Tableau Desktop they should be picked up.
stacking pivot.tflx 14.7 KB
Thanks for the reply, i have been able to create this flow up until the last pivot before the cleaning of the data. For some reason it will not let me pivot my rows to columns for the "FieldName" field.
Eben when i take your notebook from above and remove the "FieldName" form the pivot it will not let me add it back again. Am i missing a step or something?