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

[Anonymous]

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!

Date	Data	Index number
Aug 1	Lorem	1
Aug 1	ipsum	2
Aug 1	dolor	3
Aug 2	sit	1
Aug 2	amet	2
Aug 2	consectetur	3
Aug 2	adipiscing	4
Aug 3	elit	1
Aug 3	Fusce	2

[Anonymous · Accepted solution]

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

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.

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.

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:

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.

6. Results!

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.

[Anonymous]

This is amazing. Thank you!