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

SharePoint list find latest data

I've got a SharePoint list (30,000+ records) with dates, unique IDs, and a reported flow rate. Is there a way to perform a flow that would mimic a SQL query that returns the latest flow rate grouped by unique ID (updated date is the Max(Date) over the unique ID)

 

List:

ID     Rate   Date

1      0         2018-01-01

2      1         2018-01-02

1      3        2018-01-02

2      4        2018-01-03

 

Wanted:

ID     Rate   Date

1      3        2018-01-02

2      4        2018-01-03

1 ACCEPTED SOLUTION

Accepted Solutions
v-bacao-msft
Community Support
Community Support

Hi @pt1,

 

If you want to include the data of the obtained item in a CSV table, you need to use List item ID (this is the unique one in the metadata), not the ID you mentioned, which needs attention.

Add some actions to the previous Flow.

Initialize a variable ItemID, save the obtained List item ID in the variable, and replace Compose with append to array variable.

17.PNG

Configure Filter array to configure the value of Get items in From field, The condition is that the variable ItemID contains List Item ID.

18.PNG

Use Select to get the fields needed for configuration, create a CSV table, and send it as an Email body.

19.PNG

Take a try.

 

Best Regards,

Barry

Community Support Team _ Barry
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-bacao-msft
Community Support
Community Support

Hi @pt1,

 

It seems that you want to group by ID and then find the item for each group's latest date.

I am confused about the ID column. Is this your custom column?

It seems that we cannot create a column with the name ID. And since it is a unique ID, why does a list have multiple items with the same ID value.

Could you provide more explanations about your needs? Or I understand that it is wrong.

I did a test on my side and created a UID column similar to the one you mentioned, like:

11.PNG

In addition to the method of grouping items and finding the maximum value of the date, you could refer to this method:

   Use variables to hold the values of all UIDs, then use union() function to remove duplicate values.

12.PNG

   Then use Apply_to_each to traverse the array, configure Get items-Filter Query to get the maximum value of the date in each group, and use Order by to get only the item with the largest date.

   You can also use Compose to verify that the item is expected. Note that union() function is written first in Expression and then filled in the field.

13.PNG

Please take a try and let me know if you have any question.

 

Best Regards,

Barry

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

Thanks Barry,

 

It looks like the flow is working, but I don't seem to be able get the data out (ideal csv table for easy email). I've created another array variable to store the data from the compose statement, but I'm struggling to create the table. Any help would be greatly appreciated.

 

Thanks,

Paul

v-bacao-msft
Community Support
Community Support

Hi @pt1,

 

If you want to include the data of the obtained item in a CSV table, you need to use List item ID (this is the unique one in the metadata), not the ID you mentioned, which needs attention.

Add some actions to the previous Flow.

Initialize a variable ItemID, save the obtained List item ID in the variable, and replace Compose with append to array variable.

17.PNG

Configure Filter array to configure the value of Get items in From field, The condition is that the variable ItemID contains List Item ID.

18.PNG

Use Select to get the fields needed for configuration, create a CSV table, and send it as an Email body.

19.PNG

Take a try.

 

Best Regards,

Barry

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

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (1,874)