Flattening a hierarchy from 5 columns into a unique one

[tomoonot]

I'm trying to flatten a hierarchy of 5 columns into one singular column with only unique values from an original file that is copypasted into the InputTable The output column is not too important, so I am trying to create this hierarchy by ommitting it.

It is kinda working, but it's not optimal, since I cannot seem to be able to figure out, how to lookup a value and retrieve its header.
So I have to circle back a using a hardcoded sequence of 5 repeating values and then applying unique to those and then filtering them.
Also not sure if it's possible to have a table of dynamic size, since I am copy pasting into it, I would like to have it adjust automatically how many rows are not empty under the header, but no idea how to do so.

And since there are 4 Columns of Rows, in the 2nd table, where I try to flatten it, I had to pre-allocate 4-5x rows for the output of the flattenning.
In case where I had a 1000 rows, I had to pre-allocate 5000, which seemed to be quite slow.
I'm pretty sure it could be done more elegantly, dynamically and in a way that is not so resource intensive,

I'm imagining that if I could just get the header row from a formula without using the hard coded workaround and 2 helper tables, it should be possible to do the whole flattenning, removing duplicates and filtering out the output in one go, but I am struggling to find out how.

Also watched some vdeos on dynamic arrays using NamedRange# with the has/pound sign in the end, but haven't really gotten to trying it out as I am mostly getting by by using index and 1 result at a time by ROW()-1, but perhaps arrays could be better? Tried something similar a few times before but inside a named table they'd spill, not sure if I had used the correct notation at the time though.

Input table: Source data

Dymmy_Sheet - Copy.xlsx
	A	B	C	D	E	F
1	Stage	Level1	Level2	Level3	Level4	Output
2	Stage 1	A100: Level1 A	B200: Level2 AB	C300: Level3 ABC	XYZ10: Name 1	Output 1
3	Stage 1	A100: Level1 A	B200: Level2 AB	C300: Level3 ABC	XYZ10: Name 1	Output 2
4	Stage 1	A100: Level1 A	B200: Level2 AB	C300: Level3 ABC	XYZ15: Name 2	Output 1
5	Stage 1	A100: Level1 A	B200: Level2 AB	C300: Level3 ABC	XYZ15: Name 2	Output 2
6	Stage 1	A100: Level1 A	B200: Level2 AB	C300: Level3 ABC	XYZ15: Name 2	Output 3
7	Stage 1	A100: Level1 A	B200: Level2 AB	C350: Level3 ABC	XYZ20: Name 1	Output 4
8	Stage 1	A100: Level1 A	B200: Level2 AB	C350: Level3 ABC	XYZ20: Name 1	Output 5
9	Stage 1	A100: Level1 A	B200: Level2 AB	C350: Level3 ABC	XYZ25: Name 2	Output 6
10	Stage 1	A100: Level1 A	B250: Level2 AB	C400: Level3 ABC	XYZ30: Name 3	Output 7
11	Stage 2	A150: Level1 A	B300: Level2 AB	C450: Level4 ABC	SDT20: Name 1	Output 11
12	Stage 2	A150: Level1 A	B300: Level2 AB	C450: Level4 ABC	SDT20: Name 1	Output 12
13	Stage 2	A150: Level1 A	B300: Level2 AB	C500: Level4 ABC	SDT25: Name 1	Output 20
14	Stage 3	A200: Level1 A	B350: Level2 AB	C550: Level4 ABC	SDT30: Name 1	Output 25
Transform

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F14	Cell Value	duplicates	text	NO
E:E	Cell Value	duplicates	text	NO
F:F	Cell Value	duplicates	text	NO

AllHierarchyTable: Flattened table

Dymmy_Sheet - Copy.xlsx
	H	I	J	K
1	allHierarchy	Type	Test1	Test2
2	A100: Level1 A	Level1	#N/A	#VALUE!
3	B200: Level2 AB	Level2	#N/A	#VALUE!
4	C300: Level3 ABC	Level3	#N/A	#VALUE!
5	XYZ10: Name 1	Level4	#N/A	#VALUE!
6	Output 1	Output	#N/A	#VALUE!
7	A100: Level1 A	Level1	#N/A	#VALUE!
8	B200: Level2 AB	Level2	#N/A	#VALUE!
9	C300: Level3 ABC	Level3	#N/A	#VALUE!
10	XYZ10: Name 1	Level4	#N/A	#VALUE!
11	Output 2	Output	#N/A	#VALUE!
12	A100: Level1 A	Level1	#N/A	#VALUE!
13	B200: Level2 AB	Level2	#N/A	#VALUE!
14	C300: Level3 ABC	Level3	#N/A	#VALUE!
15	XYZ15: Name 2	Level4	#N/A	#VALUE!
16	Output 1	Output	#N/A	#VALUE!
17	A100: Level1 A	Level1	#N/A	#VALUE!
18	B200: Level2 AB	Level2	#N/A	#VALUE!
19	C300: Level3 ABC	Level3	#N/A	#VALUE!
20	XYZ15: Name 2	Level4	#N/A	#VALUE!
21	Output 2	Output	#N/A	#VALUE!
22	A100: Level1 A	Level1	#N/A	#VALUE!
23	B200: Level2 AB	Level2	#N/A	#VALUE!
24	C300: Level3 ABC	Level3	#N/A	#VALUE!
25	XYZ15: Name 2	Level4	#N/A	#VALUE!
26	Output 3	Output	#N/A	#VALUE!
27	A100: Level1 A	Level1	#N/A	#VALUE!
28	B200: Level2 AB	Level2	#N/A	#VALUE!
29	C350: Level3 ABC	Level3	#N/A	#VALUE!
30	XYZ20: Name 1	Level4	#N/A	#VALUE!
31	Output 4	Output	#N/A	#VALUE!
32	A100: Level1 A	Level1	#N/A	#VALUE!
33	B200: Level2 AB	Level2	#N/A	#VALUE!
34	C350: Level3 ABC	Level3	#N/A	#VALUE!
35	XYZ20: Name 1	Level4	#N/A	#VALUE!
36	Output 5	Output	#N/A	#VALUE!
37	A100: Level1 A	Level1	#N/A	#VALUE!
38	B200: Level2 AB	Level2	#N/A	#VALUE!
39	C350: Level3 ABC	Level3	#N/A	#VALUE!
40	XYZ25: Name 2	Level4	#N/A	#VALUE!
41	Output 6	Output	#N/A	#VALUE!
42	A100: Level1 A	Level1	#N/A	#VALUE!
43	B250: Level2 AB	Level2	#N/A	#VALUE!
44	C400: Level3 ABC	Level3	#N/A	#VALUE!
45	XYZ30: Name 3	Level4	#N/A	#VALUE!
46	Output 7	Output	#N/A	#VALUE!
47	A150: Level1 A	Level1	#N/A	#VALUE!
48	B300: Level2 AB	Level2	#N/A	#VALUE!
49	C450: Level4 ABC	Level3	#N/A	#VALUE!
50	SDT20: Name 1	Level4	#N/A	#VALUE!
51	Output 11	Output	#N/A	#VALUE!
Transform

Cell Formulas
Range	Formula
H2:H51	H2	=IFERROR(INDEX(InputTable[[Level1]:[Output]], INT((ROW()-2)/COLUMNS(InputTable[[Level1]:[Output]]))+1, MOD(ROW()-2, COLUMNS(InputTable[[Level1]:[Output]]))+1), "")
I2:I51	I2	=IF(allHierarchy<>"", CHOOSE(MOD(ROW()-2,5)+1, "Level1", "Level2", "Level3", "Level4", "Output"), "")
J2:J51	J2	=INDEX(InputTable[#Headers], MATCH([@allHierarchy], INDEX(InputTable, 0, MATCH([@allHierarchy],InputTable, 0)), 0))
K2:K51	K2	=IF([@allHierarchy]<>"",XLOOKUP([@allHierarchy],InputTable,InputTable[#Headers],,0,1),"")

Named Ranges
Name	Refers To	Cells
allHierarchy	=FlattenTable[allHierarchy]	J2:K2, I2:I51
Level1	=InputTable[Level1]	H2:H51, J2:K51
Level2	=InputTable[Level2]	H2:H51, J2:K51
Level3	=InputTable[Level3]	H2:H51, J2:K51
Level4	=InputTable[Level4]	H2:H51, J2:K51
Output	=InputTable[Output]	H2:H51, J2:K51
Stage	=InputTable[Stage]	J2:K51

UniqueTable: Unique values from the flattened table

Dymmy_Sheet - Copy.xlsx
	M	N	O
1	Kind	Uniques	Type2
2	Alpha	A100: Level1 A	Level1
3	Beta	B200: Level2 AB	Level2
4	Gamma	C300: Level3 ABC	Level3
5	Delta	XYZ10: Name 1	Level4
6	Epsilon	Output 1	Output
7	Epsilon	Output 2	Output
8	Delta	XYZ15: Name 2	Level4
9	Epsilon	Output 3	Output
10	Gamma	C350: Level3 ABC	Level3
11	Delta	XYZ20: Name 1	Level4
12	Epsilon	Output 4	Output
13	Epsilon	Output 5	Output
14	Delta	XYZ25: Name 2	Level4
15	Epsilon	Output 6	Output
16	Beta	B250: Level2 AB	Level2
17	Gamma	C400: Level3 ABC	Level3
18	Delta	XYZ30: Name 3	Level4
19	Epsilon	Output 7	Output
20	Alpha	A150: Level1 A	Level1
21	Beta	B300: Level2 AB	Level2
22	Gamma	C450: Level4 ABC	Level3
23	Delta	SDT20: Name 1	Level4
24	Epsilon	Output 11	Output
Transform

Cell Formulas
Range	Formula
M2:M24	M2	=IFERROR(IF(allHierarchy<>"",CHOOSE(MATCH([@Type2], {"Level1","Level2","Level3","Level4","Output"}, 0), "Alpha", "Beta", "Gamma", "Delta", "Epsilon"), ""),"")
N2:N24	N2	=IFERROR(INDEX(UNIQUE(FILTER(FlattenTable[allHierarchy], (FlattenTable[allHierarchy]<>"")*(FlattenTable[allHierarchy]<>0))), ROW()-1), "")
O2:O24	O2	=IF([@Uniques]<>"",XLOOKUP([@Uniques],FlattenTable[allHierarchy],FlattenTable[Type],,0), "")

Named Ranges
Name	Refers To	Cells
allHierarchy	=FlattenTable[allHierarchy]	M2:O24

Result table: Final result, however with a lot of intermediary steps and 2 helper tables

Dymmy_Sheet - Copy.xlsx
	Q	R	S
1	Kind	Name/ID	Stage
2	Alpha	A100: Level1 A	Stage 1
3	Beta	B200: Level2 AB
4	Gamma	C300: Level3 ABC
5	Delta	XYZ10: Name 1
6	Delta	XYZ15: Name 2
7	Gamma	C350: Level3 ABC
8	Delta	XYZ20: Name 1
9	Delta	XYZ25: Name 2
10	Beta	B250: Level2 AB
11	Gamma	C400: Level3 ABC
12	Delta	XYZ30: Name 3
13	Alpha	A150: Level1 A	Stage 2
14	Beta	B300: Level2 AB
15	Gamma	C450: Level4 ABC
16	Delta	SDT20: Name 1
Transform

Cell Formulas
Range	Formula
Q2:Q16	Q2	=IF([@[Name/ID]]<>"", INDEX(UniqueTable[Kind], MATCH([@[Name/ID]], UniqueTable[Uniques], 0)), "")
R2:R16	R2	=IFERROR( INDEX(FILTER(UniqueTable[[Kind]:[Uniques]], ( UniqueTable[Kind]="Alpha")+( UniqueTable[Kind]="Beta")+( UniqueTable[Kind]="Gamma")+( UniqueTable[Kind]="Delta") ), ROW()-1,2), "")
S2:S16	S2	=IF([@Kind]="Alpha",XLOOKUP([@[Name/ID]],Level1,Stage,,),"")

Named Ranges
Name	Refers To	Cells
Level1	=InputTable[Level1]	S2:S16
Stage	=InputTable[Stage]	S2:S16

 

[Kerryx]

Using power query

Book6
	A	B	C	D
1	Stage	Attribute	Value	Kind
2	Stage 1	Level1	A100: Level1 A	Alpha
3	Stage 1	Level2	B200: Level2 AB	Beta
4	Stage 1	Level3	C300: Level3 ABC	Gamma
5	Stage 1	Level4	XYZ10: Name 1	Delta
6	Stage 1	Level4	XYZ15: Name 2	Delta
7	Stage 1	Level3	C350: Level3 ABC	Gamma
8	Stage 1	Level4	XYZ20: Name 1	Delta
9	Stage 1	Level4	XYZ25: Name 2	Delta
10	Stage 1	Level2	B250: Level2 AB	Beta
11	Stage 1	Level3	C400: Level3 ABC	Gamma
12	Stage 1	Level4	XYZ30: Name 3	Delta
13	Stage 2	Level1	A150: Level1 A	Alpha
14	Stage 2	Level2	B300: Level2 AB	Beta
15	Stage 2	Level3	C450: Level4 ABC	Gamma
16	Stage 2	Level4	SDT20: Name 1	Delta
17	Stage 2	Level3	C500: Level4 ABC	Gamma
18	Stage 2	Level4	SDT25: Name 1	Delta
19	Stage 3	Level1	A200: Level1 A	Alpha
20	Stage 3	Level2	B350: Level2 AB	Beta
21	Stage 3	Level3	C550: Level4 ABC	Gamma
22	Stage 3	Level4	SDT30: Name 1	Delta
Table1

Code in PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Stage", type text}, {"Level1", type text}, {"Level2", type text}, {"Level3", type text}, {"Level4", type text}, {"Output", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Stage"}, "Attribute", "Value"),
    #"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns", {"Attribute", "Value"}),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "Kind", each if [Attribute] = "Level1" then "Alpha" else if [Attribute] = "Level2" then "Beta" else if [Attribute] = "Level3" then "Gamma" else if [Attribute] = "Level4" then "Delta" else "Epsilon"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Attribute] <> "Output"))
in
    #"Filtered Rows"