Create conditional index column

[martim0305]

Hello.

I want to create a new index column in power query that resets every time the value of anothe column changes.

It should look like this:

In this case, every time a new colour appears, the index increases by one based on this column's value. Can you help me doing it in power query editor?

Thanks

[Anonymous · Accepted solution]

Hi @martim0305 ,

@Anonymous Thank you for your replying!

I agree it should use Group by function. And I tried to simplify the M function:

And expand the table:

The final output is as below:

Here is the whole M function in the Advanced Editor:

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

 
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[martim0305]

Thank you so much for replying. This reply really helped me out! Thank you for being so clear answering.

[Anonymous]

I would group by Coulour, and then add a custom column that applies an index to each grouped table. Then you can just expand the new custom column. So after grouping, something like

Table.AddColumn(PriorStepOrTableName, "Indexes", each Table.AddIndexColumn([Name of TableColumn], "Index", 1,1))

Delete the grouped table column, and expand your new table column. That's it!

--Nate