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 colou...
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.

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.