The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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...
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the July 2024 Power BI update to learn about new features.