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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

 

Anonymous
Not applicable

This is amazing. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.