Flat hierarchical data table conversion?

[jaboo]

I have flat hierarchical data in a named range "Table1" that looks like below, where "#" is the value for each Category and Lvl is the hierarchy position. (although not needed for this, "##" is the cumulative sum of the current level and all child levels - thanks in part to the excellent challenge here: https://chandoo.org/forum/threads/formula-challenge-024-sum-of-items-based-on-hierarchy-level.18447/.

Is it possible to transform the input table into format for eg. the Sunburst plot shown below, (or a Treemap)? This would be for an arbitrarily long list with up to 20 levels in the hierarchy (I wouldn't make a plot of the entire set but this would allow me to pick/choose pieces to make many sub-plots. I would love to be able to do this without VBA if possible. Many thanks for any help!

Input Table:

#	##	Lvl	Name
	2	5	A1
	2	7	A2
2	2	8	A3
	10	5	B1
	5	7	B2
5	5	8	B3
	5	7	B4
	5	8	B5
5	5	9	A6
	3	5	C1
	2	7	C2
	2	8	C3
	1	9	C4
1	1	10	C5
	1	9	C6
	1	10	C7
1	1	11	C8
	1	7	C9
	1	8	C10
1	1	9	C11

The output table would look like this:

5	7	8	9	10	11	#
A1	A2	A3				2
B1	B2	B3				5
	B4	B5	B6			5
C1	C2	C3	C4	C5		1
	C6	C7	C8			1
	C9	C10	C11			1

 

[GraH - Guido]

Hi,
I think I found a way with Power Query. Not sure if you can use it (depends on your Excel version), but it created this table from your sample. File is attached.

 

[jaboo]

Hi, Thank you. I am able to use Power Query although I'm not familiar with it. Can you please explain how your solution works? What does the "Fill up" step of accomplish? And what is the purpose of the TempLabel column? Finally, how do I repeat this on a different input range? Thanks again for your time.

 

[jaboo]

* I followed the steps in your query on a different range and it works, although I'm still not sure how it works, or the best way to repeat.. But it certainly is much faster than what I was doing. Thanks!

 

[GraH - Guido]

Hi jaboo, great to read it seems to work.

I'll try to answer your questions:

1. FILL UP: since your counter "#" starts with empty, I assumed the first found value is indeed the counter for all previous empty rows. Fill up you accomplish that so the value is repeated upwards.
2. I needed "TempLabel" to accomplish the pivot without aggregation step. It creates a unique label for each of the groups of data. Notice you have several "1", or "5"... So at first, without this extra column, the pivot transformation returned errors since it tried to put "C4" and "C6" in 1 cell. I can replace this with a Power Query step, now that I explain this.
3. Finally, how do I repeat this on a different input range? It depends on how you want it to work. In the end you want to be able to create charts based on a selection is what I understand.
   
   • Either select a range of data, which you want to plot on chart, before the Power Query runs: I think we accomplish some input fields to render the Power Query "dynamic". Option: make dynamic PQ.
   • Either the Power Query does the transformation on all the data and then you dynamically select, also via input fields, the range of data you want to visualize. Option: make dynamic chart.
4. Best way to repeat? Power Query can read external data and we can add an input field that contains the path and filename to make it dynamic as well. But again, it is how you want it to work.

If needed, come back...

 

[GraH - Guido]

Forgot to upload the second file: label created in PQ and result is sorted on "label".

 

[jaboo]

Thanks, this is extremely helpful.