Forum Discussion
Helper ICalculated column to assign numeric code to dates
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!
Helper IThank 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:
Super UserYou 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.
- krdavies
Thank you!
Follow up question: is there a way to add a prefix to these numbers? For example, if I wanted all of them to start with 99.
- MasonMA
that would be something like
Date ID =
"99" & RANKX(ALL('Table'[Date]), 'Table'[Date], , ASC, DENSE)but once you add a text prefix, the column becomes a text data type rather than numeric.
- krdavies
OK so the issue with this and with what pankajnamekar25 suggested is that I need to take this column and use it in an append query with another related table, so if it's just in the table on the Power BI side, I don't think there's a way for me to do this. Is there a related statement I can use in a calculated column in Power Query?