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.

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