Power Query - Nested subgroups index on 3 sublevels

Hi,

I have a source document with columns A,B,C, and D, and I would like to automate the creation of the codes in columns E, F, and G with an Excel power query. My idea was to group and index the headers, then repeat the same for the Subheader and item. The subheader index depends on the headers, ei, each subgroup index restarts at 1.

I tried many variations using group by and adding a dependent index column. Still, I cannot do this with two levels of grouping, and I couldn't find any video of people attempting something similar.

Safe to assume this method is not what I need, is there another way to achieve this? I am pretty novice... my head hurts, haha!

I would be grateful for some assistance.

In trying to resolve your issue, I can get part way there, but I have an issue with the Sub Headers. They are all spilled from an array and they will not import into Power Query. If you can amend your workbook to make that data a "working table" or range that is not an array, I will then have a solution for you later today. It is Saturday and it is filled with activity other than Excel.

I clearly understand what you want. What I cannot do is bring the sub header table into the Power Query editor as you have arrays in that table. It needs to be a table without any arrays. Only the first two columns of data will import. I have tried to fix it, but that is up to you. If you want additional help here, then you need to make that table data only without any arrays.

My apologies; I clearly do not understand what you mean. The only tab you would use in the file I provided is the CostCentreDataSet. The other tabs are only to demonstrate the end result. The CostCentreDataSet has no arrays/formulas; it only has data. Could you kindly let me know to which tab/column the arrays you are referring to?

How do you determine what the cost type should be? And what is the issue with just using formulae for this? Why must it be done with PQ?

I can't see anything formula-wise that would be likely to bloat your file. With functions in 365, I hardly ever use PQ these days.

I'll have another look.

The sample workbook has not changed - the new tab still has only three columns, so we'd still need to know how to determine the fourth.

And are these codes correct?

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	D	E	F
2	0	0	01
3	Headers	Subheaders	02
4	Fees and Insurance	Long Service Leave Corporation	03
5	Fees and Insurance	Bank Guarantees	04
6	Fees and Insurance	Council & Statutory Fees	01
7	Fees and Insurance	Insurances	02
8	Preliminaries	Cleaning and Waste	03
9	Preliminaries	Site establishment and Protection	04
10	Preliminaries	Site Labour	05
11	Preliminaries	Temporary Services	06
12	Preliminaries	Access Equipment	07
13	Preliminaries	Builder Equipment	08
14	Preliminaries	Lift Hoisting	09
15	Preliminaries	Off Site Labour	01
16	Preliminaries	Shedding and Facilities	02

Sheet: Header and Subheaders codes

They look offset to me.

OK - please share the solution for everyone's benefit.

If by chance you asked this on other forums, you needed to tell us and provide links to the cross posts (I am sure you are aware of this, though).

And you need to mark the thread as solved, please.

You can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Please mark this thread as solved.