Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
SACooper
Helper II
Helper II

Running Countif

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

 

 

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

2 REPLIES 2
SACooper
Helper II
Helper II

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

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors