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
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
)