Data Transformation

[PN2527]

I need the output as shown in the above image. I have attached the data for reference.  

Thanks for the help.

[AngelosPachis]

Hi @PN2527 ,

You can use a multi-row formula tool to create groups of entity/units based on a parent entity ID since those appear in sequence (So entity id | unit | entity id | unit | unit | entity id ...). Then you can use a filter tool to split between entities and units and join on the parent entity ID

[apathetichell]

This works with your sample data. A few things - it uses a multi-row to separate out units and entities.  Then it filters out the stuff you don't need. It crosstabs - but then does a text to column (to create rows) as some entities have multiple units.

[mattnason1]

I was curious if I could do this without a multi-row tool since that's what was made for this type of situation.  This would not work with large data sets because the append field piece would blow up the process.  But it was fun to figure out.