cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akharns
Helper V
Helper V

Excel Get Latest

I'm wanting to get the latest or max value from an Excel table.  It looks like I ought to be able to use "List rows present in table", set the order to descending on the column I'm interested in, and set the limit to 1.  "List rows present in table" is working, returning all the rows.  If I look at "Order by:" or "Filter Query" , dynamic data is not showing me the columns in the table.  The only items shown are under "manually trigger a flow".

 

Am I on the right track?  And how can I get the table columns to be available for order and/or filter?

1 ACCEPTED SOLUTION

Accepted Solutions
v-alzhan-msft
Community Support
Community Support

Hi @akharns ,

 

For your first question, yes, you need to use a loop to see the return value event though there is only one record.

For your second question, the Sytnax usage is described here:

Use OData query operations in SharePoint REST requests

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
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

12 REPLIES 12
abm
Super User III
Super User III

Hi @akharns 

 

Please check my blog post

 

https://mydevexperience.wordpress.com/2019/09/25/microsoft-flow-excel-list-rows-present-in-a-table/

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

1. I gather that the references to the columns in the table use loose binding, they cannot be selected from Dynamic Data.

2. In your blog examples, your "order by" field "Price desc" is not in the table you show.

3. If I set the limit to one, I still need to use a loop to see the return value? (I assume your comment about looping in my other question was meant to go here.)

4. Where can I see the specs for the language to be used for filter?  It is different from the native language in Flow, where you would have to call a function to see "equal".

v-alzhan-msft
Community Support
Community Support

Hi @akharns ,

 

For your first question, yes, you need to use a loop to see the return value event though there is only one record.

For your second question, the Sytnax usage is described here:

Use OData query operations in SharePoint REST requests

 

Best regards,

Alice       

 

Community Support Team _ Alice Zhang
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

Here are details on my example:

 

My called flow does the following:

  • List Rows Present in a Table:   I'm calling this with Order By and Top Count = 1.  This successfully finds the latest record.
  • Within Apply to Each, I set a variable called ReturnRecord, from the Body of the Excel lookup step.  This successfully puts the target record in the variable.
  • In Response, I put my ReturnRecord variable as the body.

My calling flow does the following:

  • I use HTTP to call the called flow with POST.  This successfully retrieves the record from the called flow.
  • I set a variable from the body of the the HTTP step, and successfully capture the record.
  • Here is where the problem is.  I tried to use Compose to extract one field from the record, but I cannot find a syntax that will allow me to do that.

I just realized I posted twice on this subject.  The other post is https://powerusers.microsoft.com/t5/Building-Flows/Get-Last/m-p/393121/highlight/false#M44748

Does anybody have any wisdom on this?

@v-alzhan-msft , any words on this?

abm
Super User III
Super User III

Hi @akharns 

 

Could you please post your flow screenshot? What is the  field type you trying to get from the compose?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

I'm trying to get a record back.  I can get the record back, but I haven't found a way to address the individual fields in the record.

 

I'll see about capturing some screen shots.

abm
Super User III
Super User III

Hi @akharns 

 

Thanks for your reply. Is that record you are trying to get from your HTTP result from the calling flow?

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

The calling flow would get the record from the HTTP flow.  I'm almost ready to post the requested screen shots.....

abm
Super User III
Super User III

Hi  @akharns 

 

Thanks for sending me the screen shots. 

 

So what you need to do is pass the HTTP response (JSON) to flow action step JSON Parser. This way you can retrieve all the dynamics properties.

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blogPower Automate Video Tutorials

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (5,114)