Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |