Conditional Index

[Ahamor]

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.

[v-yanjiang-msft]

Hi @Ahamor ,

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

[v-yanjiang-msft · Accepted solution]

Hi @Ahamor ,

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.

[Ahamor]

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]

Hi @Ahamor ,

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

Best Regards,
Community Support Team _ kalyj

[Ahamor]

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.

[mahoneypat]

Please see this article for how to add an index to a subgroup.

Creating Subgroup Ranks in Power Query Using Table.AddRankColumn Function | Sandeep Pawar (pawarbi.g...

Pat

[Ahamor]

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