Group by a column and set index

[samueltphilip]

I have a table with a column which has 4 types of values 40HC,40ST,20ST. I aslo have an index column along with other columns. 

Can I group the table in such a way that the index will be 0,1,2,3,4.. for rows with 40 HC, then 0,1,2,3,4...for 40ST and so on. This is the table -- Results

[v-jingzhang · Accepted solution]

Hi @samueltphilip 

First group by "container type" column and perform "All Rows" operation to get a new column. 

In the new column, every table value will be like below. 

Now click the Fx icon next to the formula bar and enter the following code into the formula bar. This will add an Index column to every table value. 

= Table.TransformColumns(#"Grouped Rows", {{"All Rows", each Table.AddIndexColumn(_, "Index", 0, 1)}})

After that, click the expand icon on the column header of "All Rows" to expand the columns you want. Then reorder columns per your need. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUQr3VjAwBNJGBsEhSrE6KOJGOMSNcYib4BAHmW9i4OGMzXxs4sY4xE1wiEPMx+5+bOLGOMRN4OKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Week = _t, #"Container type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Week", type text}, {"Container type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Container type"}, {{"All Rows", each _, type table [Year=nullable number, Week=nullable text, Container type=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"All Rows", each Table.AddIndexColumn(_, "Index", 0, 1)}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(Custom1, "All Rows", {"Year", "Week", "Index"}, {"Year", "Week", "Index"})
in
    #"Expanded All Rows"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[JTaylor]

This works great.. thanks!

[wdx223_Daniel]

CalculatedColumn=COUNTROWS(FILTER(Table,Table[Container Type]=EARLIER(Table[Container Type])&&Table[Year Week]<EARLIER(Table[Year Week])))

[samueltphilip]

I want the result as Year, Week, True, Forecast, Port, ContainerType,..., Index

                                                                                            20ST              0

                                                                                            20ST               1

                                                                                            20ST                2

etc

                                                                                             40HC              0

                                                                                             40HC              1

                                                                                              40 HC             2

etc

etc