Tree (hierarchical) structure into tabular format

[mkuzner]

Hello lovely people at the Excelguru,


I am dealing with a problem of turning tree hierarchical structure that I get from my datawarehouse into tabular format to do analysis of my data and I really need some help.

Let me try to explain. I am also attachingView attachment txt data tree structure.txt the sample file. The data is exported from SAP in .txt format (separated by tabs). And the structure (as it is actually really a tree structure in SAP, familiar to table of contents for example in Word). What I am trying to do, si match this data with another table I have (doing VLOOKUP or better merging queries) and it should be connected by final IDs (in the example file I am calling them just that, final 999...). Final IDs have some parent IDs, which in tabular format should be written with every final ID. But the problem which comes up, is that not all final IDs are on the same level. Some of them have only one (main) parent ID, and some of them have several (max. 4) (I like to think about this as a Normal text in Word "belonging" to the Heading 1, 2, 3 or 4).

I did some transformation, filled the columns but I end up with the problem that the final IDs are mixed with parent ones in some columns. I do have and idea though, that I don't know how to implement. Is it possible (I'm sure it is, but I simply don't know how) to replace values that contain a certain text string with something else (replacing cells that contain word final with nothing)? I did that in Excel after import, but obviously it would be cool to make the magic happen in Power Query.

I am also attaching the Excel file to show my progress.

Thank you for all the help already in advance, either anwsering the last question or giving the idea on how to "attack" the tree structure from the begining.

Regards, Marko

 

[mkuzner]

I remembered I have another thing to ask, but 10 minutes have passed and I cannot edit my post.

When trying to replace "final" cells with nothing, I also need the cell in the next column to have an empty values (next column contains ID desciption). I'm thinking about concatenating and after replacement dividing the column again.

Thank you.

Regards, MArko

 

[p45cal]

I've been looking at your txt file to try and do something with it in Power Query (chiefly to learn for myself, I admit) and think that the depth of parent/child relation can be obtained from the number of tab characters at the beginning of each line.
I did ask a question in this same forum ( https://www.excelguru.ca/forums/sho...on-of-the-first-number-in-a-list-lower-than-n ) and have used the responses I got. My file's currently a mess of experimental queries so I'm not including it here yet but am I on the right lines with something like (click on the image to get a bigger version):

?

 

[p45cal]

am I on the right lines with something like…

Obviously not.

 

[pinarello]

Hello Marko,

how should a helper test something if your source refers to an external file ??? And how should helpers know what the final result should look like if you don't insert a manually created sample solution into the folder ???

 

[mkuzner]

Hello guys,

thank you for your responses. I have been busy a little in the last couple of weeks, among other things also with finding a solution for my problem.

p45cal you are edging closer to the solution, but I guess not just there yet.

pinarello, I must admit I don't understand your questions. I always have a .txt as a source to my data and it is structured a as tree hierachical input data. By turning it into a tabular format, I thought it would be clear what I am trying to achieve...all the rows with a final need to have all their parents (if they exist) in order to do an analysis with pivot table or in POwer BI. I haven't included a posible manual solution, that's true, but as said I thought the descpription of my goal was enough. Sorry for that and noted for future posts. Thank you for your input.

Anyhow guys, after some learning and trying of different approaches I'm proud to say I have come to a solution by using = Table.ReplaceValue function, which replaces a value based on other columns (I needed to remove "finals" on all other columns except the last two one). Please observe the file I am attaching. You will have to repair the source when opening.

Looking forward to your comments and possible tweaking of the code. Hope you don't mind Slovenian names of the steps.

Regards, Marko

 

[pinarello]

Hi Marko,

without the source (.txt file) nobody is able to check the steps in your Power Query and can not see the input structure. As a result, no one is in a position to make reasonable proposals for solutions.

 

[mkuzner]

Hi pinarello,

but I did attach the sample txt file. PLease check my first message. Probably I messed something up with attaching the file as it is somehow linked in between the message. Check the picture.

Regards, MArko

 

[pinarello]

Hi Marko,

I mean:

let
Vir = Table.FromColumns({Lines.FromBinary(File.Contents("D:\namizje\sž query\txt data tree structure.txt"), null, null, 1250)}),

without access to source data nobody is able to see what happens from step to step and who looks the source data !!!

 

[mkuzner]

Hi pinarello,

of course it doesn't work with you, because the source destinarion is obviously different (pointing to my local hard drive D).

You need to download both files to a local folder on your computer (.txt and .xlsx) and change the destination folder Power Query is looking into (see text in red):
let
Vir = Table.FromColumns({Lines.FromBinary(File.Contents("D:\namizje\sž query\txt data tree structure.txt"), null, null, 1250)}),

I guess that should solve the problem...

Regards, Marko

 

[pinarello]

Hello Marko,

I was wondering about your hint, until I found the text file hidden in the text.