This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Hi,
I want to index my subcategory here mentioned as (Brand) in data such that if head category in columnA changes then subcategory index should start back from 1. Also, I need to further expand it to the sku(Quality) level but Indexing should not be disturbed.
For small data like in example I did it by making two queries of same data. But I want to do it in one query. Formula variation for below is required for unique records of Subcategory.
=Table.AddIndexColumn([Data],"Index",1,1)
For details please see link
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Answer accepted by question author
Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Add custom index.
With randomly sized categories.
One query.
http://www.mediafire.com/file/2zotppdxrbp7pvg/07_12_19.xlsx/file
http://www.mediafire.com/file/6qv25dx4964epsa/07_12_19.pdf/file
Thanks Ashish,
You have always been remained so helpful. Question is answered.
Regards
Dilawer
Hi,
Is your question answered or do you still need help?
Thanks for reply Nathan,
Nathan, if you look at closely to my data then you will realize that there is a complexity that I need indexing against "Brand" column depending upon "Category" column values. At the same time I want to expand "Brand" column further to "Quality" level.
What changes I need to do in following formula then
=Table.AddIndexColumn([Data],"Index",1,1)
Then, When I expand "Index" column it does not allow me to expand further to "Quality" level.
From case shared from your side. I could not figure out similarity.
Regards
Dilawer
Hi Dilawerhussain,
I assume that you want to create a grouped index column. Please refer to Bill Szysz' function in the following thread:
Add a grouped index column - seems way too difficult
Please let us know if you have any further questions.
Best Regards,
Nathan