Forum Discussion
Calculated 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!
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.
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?
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"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.
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.
