none
Group By create index within groups RRS feed

  • Question

  • I am using Group By and want to get a  numbered index within each group so that each grouping category has their own index (1, 2, 3, 4, etc) that corresponds to the elements within each grouping category.

    Table has three columns: category, element

    i want to create the index to count the elements in each group

    category   element index

    A    ring     1
    A    head     2
    A    this     3
    A    that     4
    B   cat       1
    B   four      2
    C   three     1
    C   red       2
    C   blue      3

    I have this so far:

    = Table.Group(Source, {"Category"}, {{"index", each List.Generate(()=>1, each _ [Category], each _ + 1), type number}})

    This gives me the grouping by Category items but it only shows "List" in the index column. What else do I have to add so that the List expands to show rows with index numbers within each group ?

    My specific requirement is to put an index custom column within each imported text file that I am importing using the Import From Folder option. I have the file name in the imported expanded query set. I am planning on grouping by text file name, and then putting index within each, as specific file row index.



    • Edited by sitrucp Wednesday, April 20, 2016 11:23 PM
    Wednesday, April 20, 2016 11:05 PM

Answers

  • If my understanding is correct it would probably be easiest to create an additional column before expanding that adds the index-column to the individual files:

    = Table.AddColumn(PreviousStep, "Custom.1", each Table.AddIndexColumn([Custom], "Index",1,1))

    Custom being the name of the column that holds the document's tables.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by sitrucp Friday, April 22, 2016 11:09 PM
    Thursday, April 21, 2016 6:15 AM
    Moderator

All replies

  • I might be wrong, but it looks like this: https://social.technet.microsoft.com/Forums/en-US/5a89b690-abbf-4c0d-a0a2-a01fdfe0e4eb/restart-numbering-for-each-change-in?forum=powerquery
    Thursday, April 21, 2016 12:00 AM
    Moderator
  • I was going to refer him to the last post here:

    https://social.technet.microsoft.com/Forums/en-US/d9a30cd0-9dbe-48ca-8260-8fabc585bceb/power-query-insert-index-by-subcategory-ie-clustered-index?forum=powerquery

    but didn't realize that the solution is virtually the same as Curt's!

    Thursday, April 21, 2016 12:13 AM
  • Yes this or Colin's solution work fine.

    But any ideas if an index could be added to each of the individual text files in the From Folder import at the time they are imported?

    For example at the list of files stage, before they are expanded and appended, could you create a List.Generate custom column that also expands as index in each of separate expanded files?

    Thursday, April 21, 2016 5:23 AM
  • If my understanding is correct it would probably be easiest to create an additional column before expanding that adds the index-column to the individual files:

    = Table.AddColumn(PreviousStep, "Custom.1", each Table.AddIndexColumn([Custom], "Index",1,1))

    Custom being the name of the column that holds the document's tables.


    Imke Feldmann TheBIccountant.com

    • Marked as answer by sitrucp Friday, April 22, 2016 11:09 PM
    Thursday, April 21, 2016 6:15 AM
    Moderator
  • BTW: This technique could also be used for the "nested index" in the Grouping-operation:

    = Table.Group(Source, {"Category"}, {{"index", each Table.AddIndexColumn(_, "Index",1,1), type table}})

    so you were kind of close there :-)


    Imke Feldmann TheBIccountant.com

    • Proposed as answer by M. Shparber Friday, January 6, 2017 6:17 PM
    Thursday, April 21, 2016 6:47 AM
    Moderator
  • When you say 'column that holds the document's tables." you mean in the stage that lists the files, the column that has the light green "Table" in it? Screenshot below in my case that column is called getFiles (This is custom function to get all Excel files in folder which may be complicating this but lets see)

    I created following custom column based on your suggestion:

    = Table.AddColumn(#"Added Custom getFiles", "Daily Index", each Table.AddIndexColumn([getFiles], "Index",1,1))

    That gets me this

    Then at Expanded getFiles stage the result is that I get light green "Table"in expanded results, not an index.

    Thursday, April 21, 2016 4:30 PM
  • The index has been added to the tables in there (this was my understanding of the request).

    So if you expand by clicking on the arrow in "Daily Index", they will show.


    Imke Feldmann TheBIccountant.com

    Thursday, April 21, 2016 4:47 PM
    Moderator
  • Ah ok, yes the index is meant to be a counter for each table's records, starting at 1 and ending at last record.

    It should reset to 1 for each imported file's records.


    Thursday, April 21, 2016 6:35 PM
  • Strange that it doesn't do exactly that, as it does it with me.

    What does "getFiles" return?


    Imke Feldmann TheBIccountant.com

    Thursday, April 21, 2016 7:51 PM
    Moderator
  • Yes I can click on the light green Table.

    Clicking on the light green Table does expand, though it blows out all subsequent steps, and only expands one of the files, but it does give the index. So its very close.

    Clicking on the double arrow at top of Daily Index column also does expand but it does 1 for all rows, then 2 for all rows, etc.

    But its getting close to desired results ... and so far it is much easier than the methods above!

    It is doing ...

    category   element index

    A    ring     1
    A    head     1
    A    this     1
    A    that     1
    B   cat       1
    B   four      1
    C   three     1
    C   red       1
    C   blue      1

    A    ring     2
    A    head     2
    A    this     2
    A    that     2
    B   cat       2
    B   four      2
    C   three     2
    C   red       2
    C   blue      2

    A    ring     3
    A    head     3
    A    this     3
    A    that     3
    B   cat       3
    B   four      3
    C   three    3
    C   red       3
    C   blue      3

    etc


    • Edited by sitrucp Thursday, April 21, 2016 10:53 PM
    Thursday, April 21, 2016 10:51 PM
  • This is unexpected behaviour, would you mind sharing your code (preferably all, but at least some lines before and after this step.

    One question: All 3 blocks are identical (apart from the last "Index"-column). I thought that the first column stood for the filename - so this would mean that all files stand in each cell/row?


    Imke Feldmann TheBIccountant.com

    Friday, April 22, 2016 4:33 AM
    Moderator
  • Hi Davie,

    If i understand correctly, You want to put index column in each table in "getFile" column. Additionally, You want to get index for each group of IDs (A,B,C etc). If am i right, You have to use M UDF. Check my attachment and tell me is this what you want to achieve (this is only simple example).

    Example file on my GoogleDrive

    Regards

    Friday, April 22, 2016 10:48 AM
  • Yes all three blocks are the same. So each is getting an index.

    getFiles is a function to help get all excel files in folder I have redacted actual column names and put generic names ..

    (filepath) =>  
    let
        Source = Excel.Workbook(File.Contents(filepath), null, true),
        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{column1},{column2},,,,etc})
    in
        #"Changed Type"

    This function is  called from the query we are talking about as a custom column

    = Table.AddColumn(Source, "getFiles", each getFiles([Folder Path] & [Name]))

    this is what populates the file list stage (screenshot below)

    It is at this stage I created following custom column based on your suggestion:

    = Table.AddColumn(#"Added Custom getFiles", "Daily Index", each Table.AddIndexColumn([getFiles], "Index",1,1))

    So each file has 200 rows. I am trying to get an index of 1 to 200, in each of the expanded files.

    Currently this is putting index 1 in all 200 rows, then index 2 in 200 rows, and so on .. which is replicating # rows in query result to 200 x 200 rows.

    Friday, April 22, 2016 7:41 PM
  • I am behind corp firewall so can't get at the file.

    But I want index for each group of IDs (which are imported files as per above).

    What is M UDF ? Is this a Power Query function?

    Thanks

    Friday, April 22, 2016 7:43 PM
  • Having problems to put the pieces together here.

    Starting with questioning why you didn't use From Folder (Folder.Files).

    But coming back to you post with the screenshot which was supposed to show the result after you expanded "Daily Index": It looks as if you have expanded the wrong column, as the column "Daily Index" is still showing the tables. Please remove that expanding step and expand "Daily Index" instead.


    Imke Feldmann TheBIccountant.com

    Friday, April 22, 2016 7:54 PM
    Moderator
  • Ok I see, the  "Daily Index" column expansion is meant to replace the "getFiles" expansion.

    That did work.

    I now have nice index for each of the imported data files.

    Thanks Imke for persistence.

    Friday, April 22, 2016 8:45 PM
  • Davie, it's not clear to me whether you're looking for an output as Bill suggests (where the indexing starts anew for each file (as in his example file), or whether you want to continue the indexing for each file, e.g. if you have 4 A categories in file 1 and 4 A categories in file 2, then the indexes runs from 1 to 8. If the latter, then a complete solution (from importing the files) could look like the following:

    let
        Source = Folder.Files("C:\temp"),
        AddedCustom = Table.AddColumn(Source, "Data", each Excel.Workbook([Content])),
        RemovedColumns1 = Table.RemoveColumns(AddedCustom,{"Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
        ExpandedData = Table.ExpandTableColumn(RemovedColumns1, "Data", {"Data", "Kind"}, {"Data", "Kind"}),
        FilteredRows = Table.SelectRows(ExpandedData, each ([Kind] = "Table")),
        RemovedColumns2 = Table.RemoveColumns(FilteredRows,{"Content", "Kind"}),
        ExpandedData2 = Table.ExpandTableColumn(RemovedColumns2, "Data", {"category", "element"}, {"category", "element"}),
        GroupedRows = Table.Group(ExpandedData2, {"category"}, {{"FilteredTable", each _, type table}}),
        AddedClusteredIndex = Table.TransformColumns(GroupedRows, {"FilteredTable", each Table.AddIndexColumn(_,"ClusteredIndex",1,1)}),
        ExpandedFilteredTable = Table.ExpandTableColumn(AddedClusteredIndex, "FilteredTable", {"element", "ClusteredIndex"})
    in
        ExpandedFilteredTable

    In the above code, Data is the column containing the Excel tables (produced in the AddedCustom step).


    Friday, April 22, 2016 9:57 PM
  • I've created solutions for both situations, but didn't realize that you already had a solution for the other since you didn't indicate that the question was answered. Sorry 'bout that.
    Friday, April 22, 2016 11:03 PM
  • Thanks for prompt to indicate answer.

    Yes both of yours do work.

    But since I was already expanding files, Imke's solution was dead simple to implement.

    Thank you all

    Friday, April 22, 2016 11:11 PM
  • BTW: This technique could also be used for the "nested index" in the Grouping-operation:

    = Table.Group(Source, {"Category"}, {{"index", each Table.AddIndexColumn(_, "Index",1,1), type table}})

    so you were kind of close there :-)


    Imke Feldmann TheBIccountant.com

    Wow! One line of code has done what others are doing using functions and multiple steps... Very Nice!

    But how does it work? This part - each Table.AddIndexColumn(_, "Index",1,1)?

    Thanks


    Michael

    Friday, January 6, 2017 6:20 PM
  • Hi Michael,

    this is a variation of a technique described here: https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/

    But instead of creating a separate function, I've nested it in directly. Pls let me know if you need more explanation here :-)


    Imke Feldmann TheBIccountant.com

    Friday, January 6, 2017 8:18 PM
    Moderator