Create an Index within an Index

[Rjn_93]

I have a table which I want to Index within an index. This table contains dta about products and contain multiple intsances of the "Store Number". See Example Below:

However I want to Index it within an Index. So for Example there is three occurances of the Store No, so I want the desired result to be like below.

|Store No.|Product|Index|

|123         | Apple | 1

|123         | Apple | 2

|123         | Apple | 3

|122         | Banana | 1

[rubayatyasmin]

Hi, @Rjn_93 

at first create an index column in PQ starting from 1. then sort the rows in ascending or desc then close and load the data. After that create a calc column. 

here is the solution file:

https://file.io/pr6uqYnyZ2IZ

[Rjn_93]

Hi Thanks for the reply, but is there a way to do this in the Power Query using m? I want to filter out data further before the data is loaded.

[rubayatyasmin]

yes, create a custom column. 

The M code I used is

Table.AddColumn(#"Added Index", "CustomColumn", each 
    let
        CurrentStoreNo = [Store No],
        CurrentProduct = [Product],
        CurrentIndex = [Index],
        FilteredTable = Table.SelectRows(#"Added Index", 
            each [Store No] = CurrentStoreNo 
            and [Product] = CurrentProduct 
            and [Index] <= CurrentIndex),
        CountRows = Table.RowCount(FilteredTable)
    in
        CountRows
)