Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional Index

Hi,

 

I am trying to add a conditional index in column C based on data in column B. I need an incremental index if the value in cell B is repeated. So C2 starts with 1 as it's the first value then C3 is 2 as the value in B3 has been displayed a second time. Then C4 starts back at 0 as we've gotten a new value in B4. This repeats incrementally counting all values in column B. I need a way to do this power query if possible.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    Sample:

    1.Add a new step in advanced editor.

    = Table.Group(#"Changed Type", {"Column"}, {{"Index", each  Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}})

    2.Expand the new column with selecting the Index column.

    Result:

    Here's the whole M syntax:

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

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

7 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi Anonymous ,

    According to your description, here's my solution.

    Sample:

    1.Add a new step in advanced editor.

    = Table.Group(#"Changed Type", {"Column"}, {{"Index", each  Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}})

    2.Expand the new column with selecting the Index column.

    Result:

    Here's the whole M syntax:

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

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    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
      3 years ago

      If I copy and paste your formula into advanced editor I get a "Token Equal Expected" error it appears after Table.Group but I cant figure out why? Am I also supposed to be changing the underscore after "Table.AddIndexColumn(__" ?

      Thanks.

       

       

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I was able to group by my original value that needed indexed, then create an index off the table that was created and expand to show index for each value.

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        Hi Anonymous ,

        So the error disappear, the problem is resolved? Do you still need other help?

         

        Best Regards,
        Community Support Team _ kalyj

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      The table.AddRankColumn doesnt appear to be available in Office365 Excel PowerQuery as of yet unless I am missing something

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi Anonymous ,

    Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

     

    Best Regards,
    Community Support Team _ kalyj