Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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.
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.
This is amazing. Thank you!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
63 | |
53 | |
39 | |
25 |
User | Count |
---|---|
85 | |
57 | |
45 | |
43 | |
38 |