Power Query - create an index that restarts when another column changes

[MardiLinke]

I have a table of data with numbers in one column and I'd like to add a column with an index number that resets to 1 when the original column number changes. The original column is PO # below, and I'd like to add the Index column.

PO #		Index
104001		1
104054		1
104055		1
104055		2
104056		1
104056		2

[tackytechtom · Accepted solution]

Hi  @MardiLinke , 

Before:

After:

You can achieve this with these two alternatives.Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

a)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMTAwVIrVgTBNTRBMU6xMMxRmLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PO #"}, {{"Grouping", each _, type table [#"PO #"=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Grouping], "Index", 1 )),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"PO #", "Index"}, {"PO #", "Index"})
in
    #"Expanded Custom"

b)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMTAwVIrVgTBNTRBMU6xMMxRmLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO #" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PO #", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PO #"}, {{"AllRows", each SubGroupAddIndex(_), type table [#"PO #"=nullable number]}}),
    SubGroupAddIndex = (Table as table) as table =>
    let
        #"Added Index" = Table.AddIndexColumn(Table, "Index", 1, 1, Int64.Type)
    in
        #"Added Index",
        #"Expanded Groups" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Index"}, {"Index"}) 
in #"Expanded Groups"

I got the two ways from the following blog article:

https://www.tackytech.blog/how-to-swiftly-take-over-power-query/#3_Create_ranks_and_indexes

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[MardiLinke]

That is brilliant, thanks for the help - particularly how quickly you responded. It worked perfectly.