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
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.