Creating a unique ID for groups

[imunyao]

Lets say that I have an existing Dataverse table and each line item is a question in an audit. Each line item has a unique row ID and a checklist ID, but each audit does not have an ID. See table below. Would it be possible to populate the Audit ID column (as illustrated in the 'Audit ID' column) such that the first 3 sets of checklists IDs have an audit ID of 1, the next three have an audit ID of 2.. etc... and how would you do it? 

Row ID	Checklist ID	Response Data	Audit ID
1	1	A	1
2	2	B	1
3	3	C	1
4	1	D	2
5	2	E	2
6	3	F	2
7	1	G	3
8	2	H	3
9	3	I	3

[DamoBird365]

Hi @imunyao 

Here is your solution but using excel as it was easier to setup.  Same idea, list rows and then use a select to get all audit id's as an array.  These will be duplcated.  Using compose and union() you can then get the distinct values.  With the distinct values supplied as input to an apply to each, you can then filter the original value from list rows based on the audit id equal to current item.  Each loop will give you an array of rows related to each audit id.

Please consider accepting my answer as a solution if it helps to solve your problem.

Cheers
Damien

Please take a look and subscribe to my YouTube Channel for more Power Platform ideas and concepts, or take a look at my website. Thanks

[imunyao]

Sorry I think my question was poorly worded. So the Audit ID column is currently empty in my dataset; I want it to be populated as I illustrated (by grouping the first three questions/checklist IDs as audit 1, next three audits/checklist IDs as audit 2.....etc.).  So basically I want a flow that sets the first sets of questions with Audit ID 1, then when the checklist ID gets to 1 again, it sets the Audit ID to 2 etc.....

[DamoBird365]

Hi @imunyao 

I think I just misunderstood.  What will trigger flow doing this?  Will they always be multiples of 3?  How is this data getting into your table (why not tackle it at source?)

If this was running daily, for instance, you could get all rows without an id, order by row id, get rowcount using length(), divide by 3 and create an array using range (1 , length()) before updating the rows in batches of 3.

Need to understand a bit more about why and how?

Damien

[imunyao]

Yes that is a good question. So there are about 10 types of audits, each with varying length of questions (1-15). The audits are being completed in Power Apps. Instead of making 10 tables (1 for each audit) we decided to make 1 table for the results and another for the questions (for all audits). Therefore, the checklist IDs do not always stop at 3, as some audits have more than 3 questions. We are going to implement a way to stamp an audit ID in the app moving forward, but what do we do with data that is already there? 

[DamoBird365]

Hi @imunyao 

I'll open this up to the forum as I don't think Power Automate is the solution.  If this is dataverse and not teams dataverse, you could edit the data using the edit in excel feature.  

If it's Teams, maybe edit the data via a Power App gallery but long term, like you say, assign it via the App.

Damien

[imunyao]

It is Dataverse for Teams. We would have done it manually if we only had a few records unfortunately there are 5,000+ records. Is it not possible to create a flow that sets the ID as 1 until the next set of checklists starts (at 1) then increment by 1? For example, a do until Checklist ID = 1 and then increment the variable by 1?

[DamoBird365 · Accepted solution]

Hi @imunyao 

I see this requirement as a manual flow then? 

Have a variable set to 1, then…
Get Rows, followed by an apply to each, you could update the audit id with variable but have a condition if the checklist id is less than previous id +1 to variable. Might need a variable for previous id too. This won’t be very efficient but is possible.

I’m not at my pc now but would this give you something to try? Again, happy for input from others as this is a unique requirement. I can mock up a solution tomorrow if not.

Damien

[imunyao]

Thank you! Yes!  I will try this!

[imunyao]

Hi. I made a flow and it worked! Thank you!