Index Column By Category II

[brief001]

Hi All,

I have the table below, with only the two left columns. My wish is to add a column via the Power Query Editor, which should give an index value like in the yellow right column.

Who can help me get this done?

Note it must also be sorted in descending order on the date column.

[edhans · Accepted solution]

See this code @brief001 - note my dates are US format = mm/dd/yyyy. It will work with your dates though. You just need to sort however you want first.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Date", Order.Descending}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Sorted Rows",
            {"Name"}, 
            {
                {
                    "All Rows", 
                    each Table.AddIndexColumn(_, "Index", 1, 1),
                    type table [Name=nullable text, Date=nullable date, Index= nullable number]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded All Rows"

It turns this:

into this

What I did was sort accordingly, then grouped by the first column and created an ALL ROWS aggregation. Then I manually edited the M code to add an index column to that All Rows aggregation, then expanded the Date and Index when done.

This was using a simple Excel table I keyed in.

NameDate

Red	1/1/2020
Red	4/1/2020
Red	3/1/2020
Blue	1/4/2020
Blue	5/1/2020

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[negi007 · Accepted solution]

@brief001 Please follow below steps

this is your data

you can sort the data in the ascending order of color and descanding order of date. To achieve this, first you sort your data by color and then modify the code like below to have two sorts

#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending},{"Date", Order.Descending}}),

your data would be like below

then you need to groupby your table like below

then you add a custom column to your data like below

once you add the index column you can remove all column but new table column. Below is the final output

i am also attaching pbix file for your reference.

you may refer to this video as well for help - > https://www.youtube.com/watch?v=a0FqNLI0VsQ

[brief001]

Hi negi007, the first reaction was already worth gold to me. But your solution gave me a little more convenience with your examples. Also because I don't have to use the manual mutation of the M code. And thank you very much for the example file.

[negi007]

@brief001 I am happy that I was able to help you. Cheers 

[edhans · Accepted solution]

See this code @brief001 - note my dates are US format = mm/dd/yyyy. It will work with your dates though. You just need to sort however you want first.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Date", Order.Descending}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Sorted Rows",
            {"Name"}, 
            {
                {
                    "All Rows", 
                    each Table.AddIndexColumn(_, "Index", 1, 1),
                    type table [Name=nullable text, Date=nullable date, Index= nullable number]
                }
            }
        ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded All Rows"

It turns this:

into this

What I did was sort accordingly, then grouped by the first column and created an ALL ROWS aggregation. Then I manually edited the M code to add an index column to that All Rows aggregation, then expanded the Date and Index when done.

This was using a simple Excel table I keyed in.

NameDate

Red	1/1/2020
Red	4/1/2020
Red	3/1/2020
Blue	1/4/2020
Blue	5/1/2020

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[brief001]

Hi Edhans, your solution helped me immediately. And I understand your very extensive explanation. Thanks for your quick response!

[edhans]

Glad to help @brief001