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

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