cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JesseW
Frequent Visitor

Counting unique rows

Hi all,

 

I am wondering if anyone knows how to setup a flow to return a row count on the unique number of rows.

 

Simple data set to illustrate:

 

1

2

3

3

4

 

The number of unique rows is 4, instead of total number of rows being 5.

 

I am able to count using the compose action "@length(body('Get_rows')?['value'])". But cannot figure out how to dedupe..

 

Any ideas?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @JesseW,

 

Could you please share a screenshot of your flow's configuration?

Do you want to count on the unique number of rows within your Excel file?

 

I have created a Excel file (Only have one column in Excel table) on my OneDrive folder and the data structure of it as below:3.JPG

 

 I have made a test on my side, please take a try with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a Variables-> "Initialize variable" action, Name set to NumberArray, Type set to Array and Value set to empty.
  • Add a "Get rows" action, specify File name and Table name.
  • Add a "Apply to each" action, input parameter set to output of the output of the "Get rows" action.
  • Within "Apply to each" action, add a "Append to array variable" action, Name set to NumberArray, Value set to Number dynamic content of the trigger.

Note: The Number is a column in my Excel table.

  • Under "Apply to each" action, add a "Compose" action, Inputs field set to following formula:
union(variables('NumberArray'),variables('NumberArray'))
  • Add a "Compose 2" action, Inputs field set to following formula:
length(outputs('Compose'))

Image reference:4.JPG

 

5.JPG

The flow works successfully as below:6.JPG

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xida-msft
Community Support
Community Support

Hi @JesseW,

 

Could you please share a screenshot of your flow's configuration?

Do you want to count on the unique number of rows within your Excel file?

 

I have created a Excel file (Only have one column in Excel table) on my OneDrive folder and the data structure of it as below:3.JPG

 

 I have made a test on my side, please take a try with the following workaround:

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a Variables-> "Initialize variable" action, Name set to NumberArray, Type set to Array and Value set to empty.
  • Add a "Get rows" action, specify File name and Table name.
  • Add a "Apply to each" action, input parameter set to output of the output of the "Get rows" action.
  • Within "Apply to each" action, add a "Append to array variable" action, Name set to NumberArray, Value set to Number dynamic content of the trigger.

Note: The Number is a column in my Excel table.

  • Under "Apply to each" action, add a "Compose" action, Inputs field set to following formula:
union(variables('NumberArray'),variables('NumberArray'))
  • Add a "Compose 2" action, Inputs field set to following formula:
length(outputs('Compose'))

Image reference:4.JPG

 

5.JPG

The flow works successfully as below:6.JPG

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks 

 

I am trying the example with SharePoint list having more than 5k items but not working. I get threshold limit error. is there a way to do paging till we get all items then do union?

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (1,756)