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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.