Calculated column to assign numeric code to dates

[krdavies]

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

[krdavies · Accepted solution]

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!

[pankajnamekar25]

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 - YouTube

@krdavies

[krdavies]

@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. 

[krdavies]

Thank 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: 

[MasonMA]

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.

[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?

[jgeddes]

In Power Query you can use the Indexing function.
In this example I am adding the Index column and transforming it to add the prefix in one step.

= let index = Table.AddIndexColumn(YourPreviousStep, "Index", 1, 1, Int64.Type) in Table.TransformColumns(index, {{"Index", each "99-" & Number.ToText(_), type text}})

Full example code...

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtU1MNQ1MFWK1YFxjXSNzJC4xrqGJkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
  #"Changed column type" = Table.TransformColumnTypes(Source, {{"Date", type date}}),
    #"Added Index" = let index = Table.AddIndexColumn(#"Changed column type", "Index", 1, 1, Int64.Type) in Table.TransformColumns(index, {{"Index", each "99-" & Number.ToText(_), type text}})
in
    #"Added Index"

[krdavies]

I'm having a hard time executing this. In your first example, I'm not sure what 'YourPreviousStep' should refer to, and when I tried the second, it introduced a new column of dates that were unrelated to my existing date column. I'm just barely an advanced beginner so I might need some extra assistance to complete this. Thank you in advance for your patience. 

[jgeddes]

No worries. We were all beginners once. 
In M code, the 'YourPreviousStep' is the name of the table that the new column is being added to. 

In my example code you will notice that in place of 'YourPreviousStep' my code contains #"Changed column type". Changed column type is the previous step in the example M code. It is surrounded by #"" because it has spaces in the step name. (Would also be present if there were special characters etc. in the step name.)

Potentially the easiest way to add the column code is to go into your query and click on the "fx" button (by the formula bar)

Clicking on that button will auto-add the previous step name into the formula bar. From there you can add

= let index = Table.AddIndexColumn(YourPreviousStep, "Index", 1, 1, Int64.Type) in Table.TransformColumns(index, {{"Index", each "99-" & Number.ToText(_), type text}})

You would just need to change the 'YourPreviousStep' to the step added in the formula bar.

The example code I sent was not meant to be added to your query. It was soley intended to show a basic input and the desired outcome.

Hope this helps a bit.