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 wi...
Tips and Tricks
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
)