Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Rjn_93
Frequent Visitor

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

3 REPLIES 3
rubayatyasmin
Super User
Super User

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. 

 

rubayatyasmin_0-1697625149587.png

 

here is the solution file:

 

https://file.io/pr6uqYnyZ2IZ

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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.

yes, create a custom column. 

 

rubayatyasmin_0-1697625813839.png

 

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
)

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.