Running Countif

[SACooper]

Hi All,

I am trying to build a unique identifier to use in a PowerBI visual that will be the "Click Here" type element of clickable URL. I have everything working except for one small element, that thus far is bamboozling me.

I am choosing to use a date and user initials i.e 19/05/2022 SC however there are occasions where this might occur multiple times, I toyed with the idea of using the row reference but that just didn't look right --> 19/05/2022 SC 1548659 -- so what i'd like to do is have some form of Excel countif with a dyanmic range so that the 1st instance of 19/05/2022 SC is prescribed the value 1 (So that I can CONCAT these to 19/05/2022 SC 1) and then the next occurence of 19/05/2022 is given 2 and so on...

[SACooper]

@BA_Pete this is fantastic -- thank you!! worked like a charm.

[BA_Pete · Accepted solution]

Hi @SACooper ,

I can't give you the exact code as you've not provided an example of your source data, but the basic steps you'd want to follow would be thus:

1) Group your table on the Date/Initials column(s) and us the All Rows operator for the aggregation column. Call this 'data'.

2) Add an index column to each of your new nested tables in the [data] column, by adding a custom step with the following code:

Table.TransformColumns(
    previousStepName,
    { "data", each Table.AddIndexColumn(_, "Index", 1, 1) }
)

3) you can now either expand your nested tables back out and do the column merge, or you can use the same technique as above to do it to the nested tables, just swapping Table.AddIndexColumn out for Text.Combine or similar.

Pete