Data Cost Coding

[Silvo]

I'm trying to analyse some costs produced in some 3rd party software. The costs have a structure/hierarchy but I want to manipulate it with a Power Query to get it into a format that I can run Pivot Tables and similar analysis from.

Basically, each item is assigned a level. The level is linked to the next item above it with a lower level. So an item with level 4 would also be linked to the next items 3, 2, 1 and 0 on the rows above. The costs can then be looked at from a high level or a low level. The number of levels is not fixed and could go several levels deep but generally it is only 3-4 and varies within each project.

I feel like the best solution is to give each line a code but I keep running into issues and I'm sure there must be a clean way to do what I'm after but I've been trying for a few weeks on and off and I'm stuck. I have got it working in Excel using formula but I'd really prefer to get it working with a query and was hoping someone could point me in the right direction.

(Code and End result examples below)

Thanks for reading.

Level	Description	Gross Total
0	Group
1	Heading 1
2	Heading 2
3	Item	NIL
3	Item	NIL
3	Item	101,622
3	Item	0
3	Item	61,513
3	Item	0
3	Item	0
3	Item	50,018
3	Item	22,042
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	24,427
3	Item	21,181
3	Item	0
3	Item	35,012
3	Item	0
3	Item	0
3	Item	0
3	Item	13,560
3	Item	NIL
3	Item	NIL
3	Item	NIL
2	Heading 2
3	Item	9,945
3	Item	0
3	Item	7,373
3	Item	0
3	Item	0
3	Item	6,597
3	Item	2,907
3	Item	0
3	Item	INC
3	Item	0
3	Item	0
3	Item	9,945
3	Item	9,945
3	Item	0
3	Item	4,499
3	Item	0
3	Item	INC
3	Item	0
3	Item	4,902
3	Item	NIL
3	Item	NIL
3	Item	NIL
2	Heading 2
3	Item	1,080
2	Heading 2
2	Item	EXC
2	Item	0
2	Item	0
2	Heading 2
3	Item	EXC
1	Heading 1
2	Heading 2
3	Item	3,150
3	Item	900
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	24,700
3	Item	390
3	Item	3,055
3	Item	3,484
3	Item	2,400
3	Item	1,440
3	Item	2,400
3	Item	1,080
3	Item	5,920
3	Item	2,125
3	Item	2,520
3	Item	430
3	Item	1,800
3	Item	0
3	Item	NIL
3	Item	NIL
3	Item	500
3	Item	0
3	Item	350
3	Item	892
3	Item	0
3	Item	0
3	Item	850
2	Heading 2
3	Item	1,500
3	Item	1,500
3	Item	700
3	Item	6,500
3	Item	1,500
3	Item	1,500
3	Item	140
3	Item	1,440
3	Item	0
3	Item	7,000
3	Item	500
3	Item	500
3	Item	0
3	Item	1,800
3	Item	98
3	Item	8,280
2	Heading 2
3	Item	8,064
3	Item	12,600
3	Item	0
3	Item	0
3	Item	3,280
3	Item	0
3	Item	0
3	Item	21,641
3	Item	1,355
3	Item	0
3	Item	1,270
3	Item	850
3	Item	0
3	Item	16,532
3	Item	0
2	Heading 2
3	Item	2,000
3	Item	2,500
3	Item	2,500
2	Heading 2
3	Item	0
3	Item	4,234
3	Item	6,554
3	Item	18,270
3	Item	2,842
3	Item	2,030
3	Item	1,983
3	Item	0
3	Item	0
3	Item	18,293
3	Item	27,000
3	Item	3,033
2	Heading 2
3	Item	0
3	Item	3,600
2	Heading 2
3	Item	720
3	Item	2,880
2	Heading 2
3	Item	NIL
2	Heading 2
3	Item	200
3	Item	3,600
1	Heading 1
2	Heading 2
3	Item	NIL
3	Item	NIL
1	Heading 1
2	Heading 2
3	Item	44,250
3	Item	16,500
3	Item	40,300
3	Item	24,800
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	44,640
3	Item	0
1	Heading 1
2	Heading 2
3	Item	9,971
3	Item	3,718
3	Item	0
2	Heading 2
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	0
3	Item	1,500
3	Item	29,040
3	Item	EXC
3	Item	EXC
3	Item	1,110
3	Item	72
3	Item	0
3	Item	0
2	Heading 2
3	Item	NIL
3	Item	NIL
2	Heading 2
3	Item	NIL
2	Heading 2
3	Item	19,588
3	Item	7,304
3	Item	0
3	Item	0
2	Heading 2
3	Item	20,625
3	Item	0
2	Heading 2
3	Item	0
2	Heading 2
3	Item	52,500
3	Item	1,944
1	Heading 1
2	Heading 2
3	Item	NIL
3	Item	NIL
2	Heading 2
3	Item	NIL
3	Item	NIL
3	Item	NIL
2	Heading 2
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
2	Heading 2
3	Item	NIL
3	Item	NIL
2	Heading 2
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL
1	Heading 1
2	Heading 2
3	Item	4,097
3	Item	0
3	Item	14,400
3	Item	0
2	Heading 2
3	Item	0
3	Item	0
3	Item	6,750
3	Item	7,200
3	Item	14,000
3	Item	12,240
3	Item	10,000
3	Item	6,000
3	Item	0
3	Item	0
3	Item	1,500
3	Item	4,551
3	Item	1,638
2	Heading 2
3	Item	NIL
1	Heading 1
2	Heading 2
3	Item	NIL
3	Item	NIL
3	Item	NIL
2	Heading 2
3	Item	NIL
3	Item	NIL
3	Item	NIL
3	Item	NIL

This is the code I came up with using a formula but I'd really like to understand how I can do something like this in PQ. The formula I used was using Countifs and I feel I got close with Index and List.Sum and List.FirstN in PQ:

Level	Code3
0	00_00_00
1	01_00_00
2	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
3	01_01_00
2	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
3	01_02_00
2	01_03_00
3	01_03_00
2	01_04_00
2	01_04_00
2	01_04_00
2	01_04_00
2	01_05_00
3	01_05_00
1	02_00_00
2	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
3	02_01_00
2	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
3	02_02_00
2	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
3	02_03_00
2	02_04_00
3	02_04_00
3	02_04_00
3	02_04_00
2	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
3	02_05_00
2	02_06_00
3	02_06_00
3	02_06_00
2	02_07_00
3	02_07_00
3	02_07_00
2	02_08_00
3	02_08_00
2	02_09_00
3	02_09_00
3	02_09_00
1	03_00_00
2	03_01_00
3	03_01_00
3	03_01_00
1	04_00_00
2	04_01_00
3	04_01_00
3	04_01_00
3	04_01_00
3	04_01_00
3	04_01_00
3	04_01_00
3	04_01_00
3	04_01_00
3	04_01_00
3	04_01_00
1	05_00_00
2	05_01_00
3	05_01_00
3	05_01_00
3	05_01_00
2	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
3	05_02_00
2	05_03_00
3	05_03_00
3	05_03_00
2	05_04_00
3	05_04_00
2	05_05_00
3	05_05_00
3	05_05_00
3	05_05_00
3	05_05_00
2	05_06_00
3	05_06_00
3	05_06_00
2	05_07_00
3	05_07_00
2	05_08_00
3	05_08_00
3	05_08_00
1	06_00_00
2	06_01_00
3	06_01_00
3	06_01_00
2	06_02_00
3	06_02_00
3	06_02_00
3	06_02_00
2	06_03_00
3	06_03_00
3	06_03_00
3	06_03_00
3	06_03_00
3	06_03_00
3	06_03_00
3	06_03_00
3	06_03_00
3	06_03_00
2	06_04_00
3	06_04_00
3	06_04_00
2	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
3	06_05_00
1	07_00_00
2	07_01_00
3	07_01_00
3	07_01_00
3	07_01_00
3	07_01_00
2	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
3	07_02_00
2	07_03_00
3	07_03_00
1	08_00_00
2	08_01_00
3	08_01_00
3	08_01_00
3	08_01_00
2	08_02_00
3	08_02_00
3	08_02_00
3	08_02_00
3	08_02_00

And this is the final result of what I'm trying to do:

Row Labels	Sum of Gross Total
01
00
01	386,568
02	259,175
03	0
04	170,490
05	147,372
06	0
07	82,376
08	0
01 Total	1,045,981