cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

Re: Excel Get Latest

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
Highlighted
Super User III
Super User III

Re: Excel Get Latest

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 blog
Highlighted
Helper V
Helper V

Re: Excel Get Latest

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".

Highlighted
Community Support
Community Support

Re: Excel Get Latest

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

Highlighted
Helper V
Helper V

Re: Excel Get Latest

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.
Highlighted
Helper V
Helper V

Re: Excel Get Latest

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

Highlighted
Helper V
Helper V

Re: Excel Get Latest

Does anybody have any wisdom on this?

Highlighted
Helper V
Helper V

Re: Excel Get Latest

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

Highlighted
Super User III
Super User III

Re: Excel Get Latest

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 blog
Highlighted
Helper V
Helper V

Re: Excel Get Latest

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.

Highlighted
Super User III
Super User III

Re: Excel Get Latest

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 blog
Highlighted
Helper V
Helper V

Re: Excel Get Latest

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

Highlighted
Super User III
Super User III

Re: Excel Get Latest

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 blog

Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,578)