Organize Hierarchy for Raw Data as per desired output

[udles5666]

Hi Community

I have uncleansed and unorganized hierarchy as an output from SAP and I want to arrange as per my desired output. 

Can someone please give me a hand in cleaning the data set and arrange that as per my desired output? Thanks in advance. 

FYI all - This post might help lot of folks trying to extract hierarchy from SAP, export them into excel and use it to join to various other data sets.   

[AbhilashR]

Hi @udles5666, please give the attached workflow a try and let us know if this isn't what you are looking for.

[udles5666]

Hi @AbhilashR 

Thanks heaps.

Sorry, looks like I should have been more clear in the initial request but the solution doesn't work if the keyword TAI changes (which I think you have used as a wildcard)? 

Also, my maximum hierarchy in the data set goes up to 11 columns. Is there a way we can have a dynamic solution? 

Please see attached updated sample data and output for reference. Thanks again, 

[AbhilashR · Accepted solution]

Hi @udles5666, I have modified the logic to make a bit more dynamic. Take a look and please let us know if this addresses your ask. I urge you to test this across multiple scenarios and let us know if it needs further tweaks.