Forum Discussion

martim0305's avatar
martim0305
Frequent Visitor
2 years ago
Solved

Create conditional index column

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      martim0305
      Frequent Visitor
      2 years ago

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