Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Index Column Based on Date Change

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...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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's avatar
v-frfei-msft
Community Support
7 years ago

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's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous,

     

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


    Regards,
    Frank