Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Create an Index within an Index

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

Tips and Tricks

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    2 years ago

    Hi, Anonymous 

     

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        rubayatyasmin
        Community Champion
        2 years ago

        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
)