Unpivoting

[SebastianRo]

Hi, I have a dataset in the following structure, reflecting IDs of Parent Legal entities in one column, with the IDs of their Subsidiary Legal entities in a second (Subs). Some Parents have zero Subs.

Parent	Subs
654	-
843	186; 175; 325; 211
184	-
746	514; 851; 222
567	117
962	-

I want to convert that into a set where the first column indicates all the IDs )Parents + Subs) and then the second column what the parent entity is, or, if it is a parent entity itself "Parent". Like this:

ID	Parent ID
654	Parent
843	Parent
186	843
175	843
325	843
211	843
184	Parent
746	Parent
514	746
851	746
222	746
567	Parent
117	567
962	Parent

It requires splitting the Subs column by delimiter, then unpivot, but also adding a record for the Parent itself. I could not figure out the last step...anybody who can help?

[binuacs]

@SebastianRo One way of doing this

[taylor_butler]

Hi @SebastianRo, 

I made a slight modification, but similar approach to the others. I start by splitting to rows on the semicolon using Text to Columns, then replace the dashes with the Parent ID and union to ensure all IDs, including Parents with Subsidiaries are listed. Attached is a workflow for reference.

[OllieClarke]

Hey @SebastianRo how's this:

First you flag the IDs with no subs as Parent, then flip out all IDs, then you can join back on to get their parent ID and flag the fall out as Parents too.

Hope that helps,

Ollie

[OllieClarke]

Hey @binuacs I'm not sure that's right. The ID column (parent in yours) should be unique and show all 14 values, whereas your values are split over both columns

[NataliaElias]

Hi! I also gave it a try. It is probably not the most efficient way (it is my first time answering a question); but I think it works! Best, Natalia

[SebastianRo]

Thanks for this. The step missing here is the additional record for "Parent" for Parent IDs that have Subs. The 843 for example:

843	Parent
186	843
175	843
325	843
211	843

[OllieClarke]

@SebastianRo 

Think my solution does this:

[binuacs]

@SebastianRo updated workflow attached @OllieClarke thanks for pointing out the mistake