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
User Groups Public Preview

Join us for our User Group Public Preview!

Power Automate User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (74,343)