Index Column Based on Date Change

[Anonymous]

I am trying to add an index column to the below but I need the count to start over whenever the "Reported Date" changes. For example, the first 6 rows would be 1-6, but then rows 7-9 would be 1-4 and so on. 

I would also like to keep this all in one table because it's sorted the proper way now to do the date change.

And since I'm very new to writing code, here's what I have so far in my advanced editor. 

Thank you!! 

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

One sample for your reference. That like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:

M code in the power query for your reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA3MAQipVgdGnCNgIhYrqEhEFHGjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [date = _t]),
    Partition = Table.Group(Source, {"date"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition1" = Table.ExpandTableColumn(Partition, "Partition", {"Index"}, {"Partition.Index"})
in
    #"Expanded Partition1"

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank