Forum Discussion
Calculated column to assign numeric code to dates
Hello,
I have a table with data tracking instances of clients contacting a support desk. My table has an instance id that is the unique identifier for each call, then the date of the call and the user who reached out. We have numerous calls on each day and I would like to assign a unique identifier to each date so I can use this as a date identifier and append this data to another table. I believe I can use a calculated column to do this but I don't know how to create a formula for it.
Below is a sample table of what I currently have:
| Date | Instance ID | Username |
| 2025-03-10 | 10001 | anderson_marcus |
| 2025-03-10 | 10002 | bellamy_claire |
| 2025-03-10 | 10003 | chen_oliver |
| 2025-03-10 | 10004 | davidson_priya |
| 2025-03-10 | 10005 | everett_natasha |
| 2025-03-11 | 10006 | fletcher_dominic |
| 2025-03-11 | 10007 | anderson_marcus |
| 2025-03-11 | 10008 | bellamy_claire |
| 2025-03-11 | 10009 | irons_selene |
| 2025-03-11 | 10010 | jackson_rowan |
| 2025-03-12 | 10011 | chen_oliver |
| 2025-03-12 | 10012 | anderson_marcus |
| 2025-03-12 | 10013 | jackson_rowan |
| 2025-03-12 | 10014 | nguyen_callum |
| 2025-03-12 | 10015 | ortega_simone |
| 2025-03-13 | 10016 | patel_griffin |
| 2025-03-13 | 10017 | quinn_adriana |
| 2025-03-13 | 10018 | chen_oliver |
| 2025-03-13 | 10019 | anderson_marcus |
| 2025-03-13 | 10020 | thornton_jasper |
Hi all, thank you for your input but it turns out there was actually a very easy way to do this in Power Query. Steps below:
1. Use the group by function on the date column, setting the operation to "all rows", making a nested table for each date.
2. Add an index column (I used the custom starting point of 99001 and incremented at 1)
3. Expand the nested tables back out.
Each row now has a unique identifier based on its date!
11 Replies
Hi all, thank you for your input but it turns out there was actually a very easy way to do this in Power Query. Steps below:
1. Use the group by function on the date column, setting the operation to "all rows", making a nested table for each date.
2. Add an index column (I used the custom starting point of 99001 and incremented at 1)
3. Expand the nested tables back out.
Each row now has a unique identifier based on its date!
Hello
Use Rankx
Date ID = RANKX( ALL('Table'[Date]), 'Table'[Date], , ASC, Dense )
If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.
Thanks,
Connect with me on:
LinkedIn |
Data With Pankaj - YouTubeThank you for your reply. In trying this I'm getting a 'Token Literal Expected' error after ASC,. I am not sure how to correct this error. A screenshot is included wtih my table names covered up for confidentiality reasons:
You are in Power Query. The solution given to you is in Power BI. so go to Data View, select the table, choose New Column and paste the DAX formula there.
Not in Transform Data / Power Query Editor.
pankajnamekar25 is there a way to do a similar function in Power Query? I need to use the column to perform an append query afterwards.
