• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Numbering Grouped Data in Power Query

You are here: Home / Power Query / Numbering Grouped Data in Power Query
Numbering Grouped Data in Power Query
January 24, 2019 by Mynda Treacy

Numbering items within grouped data is easy with an Excel COUNTIF formula, but numbering grouped data in Power Query requires a few more steps. For example, taking the data below, in column C I’ve numbered the Sub Areas within each Area:

Numbering Grouped Data in Power Query

Notice they don’t all have the same number of sub areas.

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Steps for Numbering Grouped Data in Power Query

Step 1:

I started by formatting my source data in an Excel Table, called ‘Table1’:

Table1

Step 2:

Load to Power Query. In Excel 2016 onward go to the Data tab > From Table/Range:

load to Power Query

In earlier versions of Excel go to the Power Query tab > From Table.

This will load the data to Power Query and launch the Power Query Editor window.

Step 3:

In the Power Query editor window Add an Index Column (Note: this step isn't strictly required in this scenario, I just added it out of habit)

add index column

Tip: the index can start at 0 or 1 as it makes no difference.

Step 4:

Group Rows

group rows

This will open the Group By dialog box:

group by

The data is now grouped into tables for each Area. Clicking in the white space beside one of the ‘Count’ column’s tables displays the underlying data in the preview pane at the bottom of the Power Query window, as you can see below:

click white space

Step 5:

Add a Custom Column to index each grouped table:

add a custom column

Use the Power Query Table.AddIndexColumn function to Index the Count column created in the previous Group By step:

index the Count column

In English the formula translates to:

= Table.AddIndexColumn([Count]. Call this new column "Sub Area No.", 1, 1)

Add an Index column to the individual tables in the [Count] column called ‘Sub Area No.’. starting the numbering at 1 and increment by 1

In the Power Query editor window, we now have a new column called ‘Custom’ that contains our individually indexed tables we just added:

individually indexed tables

If you click in the white space beside one of the Tables in the ‘Custom’ column you’ll see a preview showing the new column for Sub Area No.:

preview shows new column

Step 6:

Delete the Count column as we no longer need it. Just select the Count column header and press the Delete key:

delete the Count column

Step 7:

Expand the Tables in the Custom column

expand the tables in the Custom column

Notice that I deselected the ‘Area’ column as we already have this. And I’ve deselected ‘Use original column name as prefix’.

Now I have my tables expanded and the ‘Sub Area No.’ column contains index for each Area:

expand the tables in the Custom column

Step 8:

Delete the ‘Index’ column as we no longer need it.

Step 9:

Set the Data types for each column by clicking on the data type icon in the left of each column header:

set data type

Step 10:

Close & Load. Now you’re ready to close the query editor and load it to a Table in the Excel workbook, or the Data Model a.k.a. Power Pivot, or a PivotTable etc.

close & load

Numbering Grouped Data in Excel

If you don’t need your data numbered in Power Query you can achieve the same results with an Excel COUNTIF formula like so:

same results with an Excel COUNTIF formula

Pay close attention to the use of absolute and relative references:

=COUNTIF($A$5:A5,A5)

Thanks

Thanks to fellow Excel MVP, Ken Puls, for pioneering the Power Query technique here.

Please Share

If you liked this please share it with your friends and colleagues.

Numbering Grouped Data in Power Query

More Power Query Posts

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, ‘if or’ and ‘if and’, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and they’re easily updated as they’re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.


Category: Power Query
Previous Post:vba like operatorVBA Like Operator
Next Post:Excel Five Star Rating ChartExcel Five Star Rating Chart

Reader Interactions

Comments

  1. Joe Denker

    January 6, 2023 at 11:40 am

    This is excellent material! I saw this method elsewhere at one point, but when I went looking for it again, I came across this and this is much better presented. Thanks!

    Reply
    • Catalin Bombea

      January 6, 2023 at 2:17 pm

      Thank you Joe for your feedback!

      Reply
    • Catherine A Bhaskar

      January 11, 2023 at 10:08 am

      Agreed! Thanks for the easy step-by-step!

      Reply
  2. David

    May 29, 2021 at 6:06 am

    This came in super helpful. Thanks for taking the time to write and post this!

    Reply
    • Mynda Treacy

      May 29, 2021 at 8:46 am

      Our pleasure, David 🙂

      Reply
  3. Dror Lichtenberg

    December 3, 2020 at 2:47 am

    Amazing capability and well-written instructions. Thank you for sharing!

    Reply
    • Mynda Treacy

      December 3, 2020 at 10:16 am

      Thanks so much, Dror! Great to know it was helpful.

      Reply
  4. randall

    June 7, 2020 at 6:49 pm

    So I did this for a field in my table called Locations. (I’m trying to count the number of open tickets for each corporate location and my main table contains ALL open tickets). How would I finish this off by doing that – example
    albany – 7
    Allentown – 6
    ect.
    Thanks in advance if you have time to answer. I appreciate it.

    Reply
    • Mynda Treacy

      June 8, 2020 at 9:24 am

      Hi Randall, I’d use a PivotTable to do the count by location. If you’re stuck, please post your question on our Excel forum and you can upload a sample Excel file so we can help you further.

      Reply
  5. Nathan

    May 6, 2020 at 3:35 am

    This saved my bacon. Thank you!

    Reply
    • Mynda Treacy

      May 6, 2020 at 9:19 am

      🙂 glad I could help!

      Reply
  6. Yolandi Dorssopoulos

    November 7, 2019 at 6:54 pm

    Thanks for this, very straight forward and simple to follow

    Reply
    • Mynda Treacy

      November 8, 2019 at 9:31 am

      Glad you found it helpful, Yolandi!

      Reply
    • Philip Treacy

      November 8, 2019 at 9:46 am

      You’re welcome

      Reply
  7. Sylvain

    September 2, 2019 at 12:37 pm

    Hello,

    Much appreciate the step by step process. Just what I needed! To go one step further I had to find the maximum value within the “Subarea” column to thus do a filter. Table.max to the rescue! Could not have done it without you. Thanks indeed!

    Reply
    • Mynda Treacy

      September 4, 2019 at 8:55 am

      Glad I could help, Sylvain! 🙂

      Reply
  8. Francesca

    July 12, 2019 at 11:55 pm

    Thank you very much for this super-useful post. I often need to use such a grouping in my data. However, I had an issue where, after expanding the Custom column, data types I had previously set or modified went lost and each column in the table had reverted to data type: Any. Can you help shed some light on this issue?

    Reply
    • Mynda Treacy

      July 13, 2019 at 7:21 am

      Hi Francesca,

      This is common in Power Query, i.e. many processes will remove the data type settings. I recommend using the ‘Detect Data Type’ tool on the Transform tab to quickly set data types again.

      Mynda

      Reply
  9. Neil Boisen

    January 27, 2019 at 6:30 pm

    Important note: If your list is sorted, the sorting and indexing may be unstable or incorrect. To avoid this issue, you can surround your Table.Group code with the Table.Buffer function in the advanced editor, so that the sort order remains stable.

    Here is an example that buffers, groups and adds the index all in one line:

    #”Grouped Rows” = Table.Buffer(Table.Group(#”Sorted Rows”, {“SpecialityID”, “DesignationID”}, {{“GroupedRows”, each Table.AddIndexColumn(_,”Index”,1,1), type table}})),

    Here are some references to this issue:
    https://www.excelando.co.il/en/powerquery-remove-duplicates-bug-workaround/
    https://social.technet.microsoft.com/Forums/WINDOWS/en-US/30e9ab27-23a5-465b-a0c4-36e4e48ce2db/bug-or-feature-when-querying-sqlserver-data?forum=powerquery

    Reply
    • Mynda Treacy

      January 27, 2019 at 8:16 pm

      Thanks for sharing, Neil.

      Reply
      • Ivo Giulietti

        April 15, 2020 at 8:01 am

        You literally saved my life. I was doing this with a table that was transactions per product ID. I wanted to have the last transaction per product and per date ( for each transaction). When I did the self join, everything got messed up. Thank you very much.

        Reply
        • Mynda Treacy

          April 15, 2020 at 8:44 am

          Glad I could help 🙂

          Reply
  10. Frans

    January 26, 2019 at 12:34 pm

    Hi – I downloaded the Excel workbook from the shortcut in the post and Windows 10 professional reported a Trojan virus in the file: Win32/Spursint.F!cl

    Reply
    • Philip Treacy

      January 26, 2019 at 12:46 pm

      Hi Frans,

      This is an issue with your Windows Defender software. If you search for ‘win32/spursint.f cl false positive’ you’ll see many mentions of this issue.

      If you want to be sure, you can go to https://www.virustotal.com/#/home/url and supply the URL to the workbook and VirusTotal will scan it for you.

      I’m happy to email the file to you if you like?

      Regards

      Phil

      Reply
    • Frans

      January 26, 2019 at 1:06 pm

      Thanks Phil for the quick reply and my apologies for raising this when it has turned out to be a ‘false positive’ – I thought it best to report it to you in case it was a real virus. Thanks for the offer to email the file – but I have recreated the sample data manually. PS: I always find your site’s training programmes and newsletters very informative. Kind regards, Frans

      Reply
      • Philip Treacy

        January 26, 2019 at 1:46 pm

        Hi Frans,

        No worries, always best to be safe.

        Great to know you enjoy our site and newsletters.

        Thanks

        Phil

        Reply
  11. osiel

    January 25, 2019 at 12:41 am

    Mil gracias Mynda, entendi la formula de Table.AddIndexColumn :). muchas gracias por los post siempre son geniales y aprendo mucho. Te envio un fuerte abrazo!.

    Reply
    • Mynda Treacy

      January 25, 2019 at 10:44 am

      De nada 🙂

      Reply
  12. Adrian

    January 24, 2019 at 9:04 pm

    What do we need the first index column for?

    Reply
    • Mynda Treacy

      January 24, 2019 at 9:38 pm

      Good question, Adrian! In this scenario you can get away without the first Index column, but often you’ll find you need it to avoid getting an Expression Error. I just added it out of habit 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.