cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nico_Branuelo
Helper II
Helper II

Add index number/row number column that resets to 1 every new data comes in

Hello All,

 

I hope someone can help me with this. I have a table in PowerBI that loads data everyday (additional 10+ rows daily). I want to assign an index number every time the new data comes in but it should reset to 1 every day. I need it to be in the table so that it can be used in a filter/slicer.


Sample table below.

 

Thank you everyone!

 

DateDataIndex number
Aug 1Lorem1
Aug 1ipsum2
Aug 1dolor 3
Aug 2sit 1
Aug 2amet2
Aug 2consectetur 3
Aug 2adipiscing 4
Aug 3elit1
Aug 3Fusce 2
1 ACCEPTED SOLUTION
OfTheHair
Helper I
Helper I

1. Open Query Editor, select your table. Select the Date column and select GROUP BY.

OfTheHair_0-1659619863130.png

 

2. Group By on the Date column, select ALL ROWS as the transformation/calculation. The name you give the calculation doesn't really matter, but we are going to need it in a following step. Here I just go with the generic name the Dutch language settings pre-fill. Select OK.

OfTheHair_2-1659619963404.png

 

3. You should now have a table with two column. 'Date' and whatever you called the 'All Rows'-transformation. All duplicate dates should be removed.

OfTheHair_3-1659620026328.png

 

4. Go to Add Column > Add Custom Column and enter the following M-code. Then click OK. The "column" you put in is actually the grouped table of step #2. 

 

Table.AddIndexColumn([Aantal], "Index", 1,1)

 

The table should look like this:

OfTheHair_4-1659620142441.png

 

 

5. Remove the original Date and Data columns ("Date" and "Aantal" in my example). Click the arrow-icon in the right corner of the new column with Table-values ("Aangepast" in my example). Deselect the option to add the original column name as a prefix. Click OK.

OfTheHair_5-1659620317310.png

 

6. Results!

OfTheHair_6-1659620349843.png

 

 

This should work on each refresh of the data. Do note that it doesn't account for the "order" if that doesn't happen in the source.

 

View solution in original post

2 REPLIES 2
OfTheHair
Helper I
Helper I

1. Open Query Editor, select your table. Select the Date column and select GROUP BY.

OfTheHair_0-1659619863130.png

 

2. Group By on the Date column, select ALL ROWS as the transformation/calculation. The name you give the calculation doesn't really matter, but we are going to need it in a following step. Here I just go with the generic name the Dutch language settings pre-fill. Select OK.

OfTheHair_2-1659619963404.png

 

3. You should now have a table with two column. 'Date' and whatever you called the 'All Rows'-transformation. All duplicate dates should be removed.

OfTheHair_3-1659620026328.png

 

4. Go to Add Column > Add Custom Column and enter the following M-code. Then click OK. The "column" you put in is actually the grouped table of step #2. 

 

Table.AddIndexColumn([Aantal], "Index", 1,1)

 

The table should look like this:

OfTheHair_4-1659620142441.png

 

 

5. Remove the original Date and Data columns ("Date" and "Aantal" in my example). Click the arrow-icon in the right corner of the new column with Table-values ("Aangepast" in my example). Deselect the option to add the original column name as a prefix. Click OK.

OfTheHair_5-1659620317310.png

 

6. Results!

OfTheHair_6-1659620349843.png

 

 

This should work on each refresh of the data. Do note that it doesn't account for the "order" if that doesn't happen in the source.

 

This is amazing. Thank you!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors